Matching and combining information on two separate worksheets

[moparfamily]

Is there a way to do the following?

One worksheet has the following information

02 - Sandee
03 - Scott
156 - Kali
300 - Tom

The other worksheet has

02
156
300
02
03


What I need to do is if the number from the first worksheet is in a column
in the second worksheet. I need to put the corresponding name with the
number (no matter how many times the number shows up).

Can someone help me with this?

Thanks

 

[Max]

One crack at this ..

This source data is assumed in Sheet1, in A1:A4 (all in 1 col)

02 - Sandee
03 - Scott
156 - Kali
300 - Tom

And this is assumed in Sheet2, in A1:A4 (all are text numbers)

02
156
300
02
03

In Sheet2,
Put this in B1, normal ENTER will do
=INDEX(Sheet1!A$1:A$4,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(A1,Sheet1!A$1:A$4)),),0))

Copy down to return desired results, viz.:
02 02 - Sandee
156 156 - Kali
300 300 - Tom
02 02 - Sandee
03 03 - Scott

Success? hit the YES below

 

[Ashish Mathur]

Hi,

You may use a VLOOKUP() function

=vlookup(sheet2!A2,sheet1!$A$2:$B$5,2,0)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com