Take data from 1 list and put with match in another.

[Fcroft]

Okay, so I have 2 lists. One has about 27,000 entries and the other has about 10,000. The list with 27,000 has an entry and then a code attached. I need to get the codes from that list to their corresponding match on the list of 10,000.

Now I'm sure there is an easier way, and if there is please use that way, but I went ahead and was going to simply do a vlookup and bring the matches over that way. For some reason, my Vlookup formula isn't returning right. I haven't used Vlookups in a while, but the formula should be correct if my memory serves me (guessing it doesn't).

I attached an example of my problem with my faulty vlookup formula. Basically I want to take match the codes in A:A to the correct Entries in G:G.

Is there an easier way to purge data that doesn't have a matching Entry in both columns?

Thanks.

[JBeaucaire]

VLOOKUP is perfectly fine for the task of getting the code from the 27k list onto the 10k list.

Col A - 27k values
Col B - 27k codes

Col G - 10k values to match

Assuming row1 is your titles and the data starts in row2..in H2, this, copied down:
=VLOOKUP(G2, A:B, 2, 0)

That will give you a list of matching codes and an error message on the ones that don't match.

Turn on the AutoFilter and filter that column for all the error codes, delete all the values left visible, then turn off the AutoFilter, you will be left with only matching values.

[Fcroft]

Sorry, forgot attachement.

[JBeaucaire]

You're misusing VLOOKUP. VLOOKUP() reads the first column in a range of columns for a match, and when a match is found in the first column, a value from the same row in a column to the right is returned.

For your data format, swap the two columns and this will work:
=VLOOKUP(G2,Table,2,FALSE)


Else, you will have to change methods, this in H2 and copied down:
=INDEX(A:A, MATCH(G2,B:B, 1))

[Marcol]

Another way might be
In G2

Please Login or Register  to view this content.

Or to hide #N/A

Please Login or Register  to view this content.

Drag/Fill Down

Hope this helps

[Fcroft]

Hah, yeah I just swapped the two columns that I was using as a table and it works now. I guess every other time I've used Vlookup, I just got lucky with the fact that the data was in the right order. Thanks and I'll go from here.