Lookup where column data does not match

  • Thread starter Thread starter NTK
  • Start date Start date
N

NTK

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
 
=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.
 
thanks Dave, that appears to be working

Dave Peterson said:
=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.
Click to expand...
 
Back
Top