Lookup where column data does not match
 

Hi,

I have a problem where for example;

Sheet1 column A1 contains data
12345678
23456765

Sheet2 column A1 contains data
Joe Bloggs12345678
Jane Bloggs23456765


An so on and so on
Can someone help me with a formul to look one set of data against the other
and put the result on sheet1

A1 B1
12345678 Joe Bloggs12345678
23456765 Jane Bloggs23456765

Any help is appreciated,
Thanks

Lookup where column data does not match
 

=INDEX(Sheet2!A1:A100,MATCH("*"&A1,Sheet2!A1:A100, 0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.



NTK wrote:

Hi,

I have a problem where for example;

Sheet1 column A1 contains data
12345678
23456765

Sheet2 column A1 contains data
Joe Bloggs12345678
Jane Bloggs23456765

An so on and so on
Can someone help me with a formul to look one set of data against the other
and put the result on sheet1

A1 B1
12345678 Joe Bloggs12345678
23456765 Jane Bloggs23456765

Any help is appreciated,
Thanks

--

Dave Peterson

Lookup where column data does not match
 

thanks Dave, that appears to be working

"Dave Peterson" wrote:

=INDEX(Sheet2!A1:A100,MATCH("*"&A1,Sheet2!A1:A100, 0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.



NTK wrote:

Hi,

I have a problem where for example;

Sheet1 column A1 contains data
12345678
23456765

Sheet2 column A1 contains data
Joe Bloggs12345678
Jane Bloggs23456765

An so on and so on
Can someone help me with a formul to look one set of data against the other
and put the result on sheet1

A1 B1
12345678 Joe Bloggs12345678
23456765 Jane Bloggs23456765

Any help is appreciated,
Thanks

--

Dave Peterson