I am checking to see if any values in column C match with values from column B

Question

I need preferably a formula or macro in excel 2013 to do the following:

1. Check if any given values in column C match with values from column B.

2. If they do I want to take the corresponding value from the same row in column A as the matched items in column B.

3. I then want to take those values from column A and put them in the same rows in column D.

Specifically, I am checking to see if any ID's in column C match with ID's from column B. If they do I want to take the corresponding city ID from column A in the same row as the matched items in column B. I then want to take those values from column A and put them in the same rows in column D.

I used this formula =VLOOKUP(C6; A2:B14; 1; FALSE) but it returns #N/A

Answer 1

VLOOKUP will always use the first column as lookup_array. But in your case, you are using the second column for lookup_array, and wanting to return the value in the first column. So VLOOKUP is not appropriate.

Depending on your version of Excel, you could use INDEX(MATCH or XLOOKUP:

=INDEX($A$2:$A$14,MATCH(C2,$B$2:$B$14,0))
=XLOOKUP(C2,$B$2:$B$14,$A$2:$A$14)