VLOOKUP to match numbers to strings of text

**rtabaldi** · 07-18-2011, 08:02 PM

Hello,

I am trying to transfer numbers from one excel sheet to another excel sheet. The old excel sheet has numbers in one column that correspond to a particular string of text in the column next to it. The new excel sheet I want to transfer these numbers to the same strings of text, but there is a second column with more information making the strings of text appear multiple times.

For example. Excel sheet 1, Column A and B

12345 - Green 2
12346 - Red 3
12347 - Yellow 4
12348 - Blue 1

Excel Sheet 2, Column A and B
Green 2 - A
Green 2 - B
Green 2 - C
Red 3 - A
Red 3 - B
Red 3 - C
Yellow 4 - A
Yellow 4 - B
Yellow 4 - C
Blue 1 - A
Blue 1 - B
Blue 1 - C

So I would want the 123... number to match up with the same color name in sheet two. So in the second sheet all Green 2's would have the 12345 number next to it in a separate column.

I tried to play around with VLOOKUP, but I am not successful so far. Any ideas? I'm willing to do an extra leg of work if this is not easy/doable as is.

**JBeaucaire** · 07-18-2011, 08:23 PM

VLOOKUP won't work because your match column is on the right instead of the left. Have to resort to an INDEX/MATCH which has not restrictions like that.

In Sheet2 C1 and copied down:

=INDEX(Sheet1!A:A, MATCH(A2, Sheet1!B:B, 0))

**rtabaldi** · 07-18-2011, 09:14 PM

Thank you. I am actually able to move the data around and completely spaced on my column positioning. I just moved my match column to the left and it worked.

Used this Formula:
=VLOOKUP(A2, A$ :B$ ,2,False)

Thank you for you help....though I was unable to get yours to work. I am not sure if I was entering it wrong, but the MATCH(A2, Sheet1!B:B, 0)) was returning #N/A......actually, now that I think about it, I did not check my spacing. I had to use =trim(cell) to get rid of extra spaces in sheet 1. So I bet Index would work.

Thanks again.

Ryan