match - Excel: Check if Cell value exists in Column, and return a value in the same row but different column

Question

After checking if a value exists in a column, I want to return the value of the cell in the same row to a different column.

Specifically, I am checking to see if any values in column O match with values from column A. If they do I want to take the corresponding value from cells in the same row as the matched items in column A, but these values are in column f. I then want to take those values from column f and put them in the same rows as the values from column O.

This is the formula I've tried:

=IF(ISNA(MATCH(O2,$A$2:$A$1589,0)),"no match", VLOOKUP(O2,$A$1:$z$14000,16,FALSE))

This returns a "no match" for all the cells in the column P. I tried messing around with the col_index_num in the VLOOKUP function, but the best I can do is get it to return zeros, which aren't the right values anyway.

Answer 1

I think the following formula should give you what you are trying to get. If I understand your question correctly, you want to return the value in column F that is in the same row as the match: hence, the range I use for column F is the same length as the range for column A.

  =IFERROR(INDEX($F$2:$F$1589,MATCH(O2,$A$2:$A$1589,0),1),"no match")

Working outward, here is what is going on.

• The match function is looking in column A for an exact match of the value in O2.

  If it finds a match, it returns the relative position of the matching value in the column A lookup range.

  If it finds no match, it returns an error value.

• The index function returns the value in the i th row and j th column of the index range, in this case the row that was found in the match, and the first (and only) column in the index range, column F.

• Finally, those two functions are wrapped in an IFERROR function, to catch the error value that will be generated if there is no match, and return instead the string "no match".

This formula would be entered in cell P2 and copied down through the last row of data in column O.