Index Match & Duplicate values

In Column A, I have a list of values which act as a "ID number" for a location. In Column B, I have a list of location numbers that correlate with the ID number found in Column A. I also have Column C, where the values represent the same list of location numbers found in Column B in a random order, but do not have a ID number for that location correlating to them, like Column B does in its relationship to Column A.

I'm trying to match Column B with Column C. If B and C are a match, I'd like the value in Column A that relates to Column B to placed in Column D (which is empty), which will act as the ID number for Column C.

To achieve this match, I used the formula: =INDEX(A:A,MATCH(C2,B:B,0))

Unfortunately, my spreadsheet has many duplicate values that need to be matched in a repetitive manner and the formula is overlooking them, rendering the value in Column D as "N/A". Is there a way to improve the formula to calculate all duplicates and not overlook them.

If you are getting #N/A, this implies that the formula was not able to find an exact match in column B - perhaps you have leading or trailing spaces, or your Location numbers in column B are text values but those in column C are numeric values (which are not the same to Excel), or vice-versa. If it is the latter, then you can try either of these in D2:

=INDEX(A:A,MATCH(C2&"",B:B,0))

or:

=INDEX(A:A,MATCH(C2*1,B:B,0))

Hope this helps.

Pete

Hi Pete, Thanks for your help.

I should have clarified. The bulk of the N/A's can be ignored as they won't match the location ID. My problem is there is 2,570 station IDs (Column A) and the same amount of values in Column B. For whatever reason, both your formulas and mine, only return a match for 2,466 values, instead of the 2,570. I've found a few instances where 3 of 4 duplicates will have the correct value returned, but the fourth one is ignored entirely. Does this help?

Perhaps that value which is ignored is of a different format to the other three (i.e. text instead of number, or vice-versa). Copy one of the cells where there is a match into the cell that doesn't match in order to correct the format.

Hope this helps.

Pete