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?
This question is related to
excel
excel-formula
The answer is
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.
You could also simply link both cells, and have an =Cell formula in each column like, =Sheet2!A2 in Sheet 1 A2 and =Sheet2!B2 in Sheet 1 B2, and drag it down, and then sort those two columns the way you want.
- If they don't sort the way you want, put the order you want to sort them in another column and sort all three columns by that.
- If you drag it down further and get zeros you can edit the =Cell formula to show "" IF there is nothing. =(if(cell="","",cell)
- Cutting, pasting, deleting, and inserting rows is something to be weary of. #REF! errors could occur.
This would be better if your unique items change also, then all you would do is sort and be done.
You could also use INDEX MATCH, which is more "powerful" than vlookup. This would give you exactly what you are looking for:
Source: Stackoverflow.com