How can I sort one set of data to match another set of data in Excel?

Question

I have a list of data that is not in alphabetical or numerical order. I want to sort a second list of the same date to match the first list. I cannot change the order of the data. My goal is to paste additional data from the second set back into the first data set.

**DATA SET A**           **DATA SET B**
22350   ____              BH160   100
22355   ____              22350   125
BH160   ____              BH190   200 
BH190   ____              22355   150

I would like to get the numerical value from column 2 of DATA SET B to show up in a new column of DATA SET A. For example, I want 125 to show up in line 1, column 2 of DATA SET 1.

My list contains about 200 unique numbers and I need to update it every week.

Can someone help me? Maybe with a macro that I could use each week?

Answer 1

You could also use INDEX MATCH, which is more "powerful" than vlookup. This would give you exactly what you are looking for:

Answer 2

You can use VLOOKUP.

Assuming those are in columns A and B in Sheet1 and Sheet2 each, 22350 is in cell A2 of Sheet1, you can use:

=VLOOKUP(A2, Sheet2!A:B, 2, 0)

This will return you #N/A if there are no matches. Drag/Fill/Copy&Paste the formula to the bottom of your table and that should do it.