Check if Cell value exists in Column, and then get the value of the NEXT Cell
ExcelExcel Problem Overview
After checking if a cell value exists in a column, I need to get the value of the cell next to the matching cell. For instance, I check if the value in cell A1
exists in column B
, and assuming it matches B5
, then I want the value in cell C5
.
To solve the first half of the problem, I did this...
=IF(ISERROR(MATCH(A1,B:B, 0)), "No Match", "Match")
...and it worked. Then, thanks to an earlier answer on SO, I was also able to obtain the row number of the matching cell:
=IF(ISERROR(MATCH(A1,B:B, 0)), "No Match", "Match on Row " & MATCH(A1,B:B, 0))
So naturally, to get the value of the next cell, I tried...
=IF(ISERROR(MATCH(A1,B:B, 0)), "No Match", C&MATCH(A1,B:B, 0))
...and it doesn't work.
What am I missing? How do I append the column number to the row number returned to achieve the desired result?
Excel Solutions
Solution 1 - Excel
Use a different function, like VLOOKUP:
=IF(ISERROR(MATCH(A1,B:B, 0)), "No Match", VLOOKUP(A1,B:C,2,FALSE))
Solution 2 - Excel
After t.thielemans' answer, I worked that just
=VLOOKUP(A1, B:C, 2, FALSE)
works fine and does what I wanted, except that it returns #N/A
for non-matches; so it is suitable for the case where it is known that the value definitely exists in the look-up column.
Edit (based on t.thielemans' comment):
To avoid #N/A
for non-matches, do:
=IFERROR(VLOOKUP(A1, B:C, 2, FALSE), "No Match")
Solution 3 - Excel
How about this?
=IF(ISERROR(MATCH(A1,B:B, 0)), "No Match", INDIRECT(ADDRESS(MATCH(A1,B:B, 0), 3)))
The "3" at the end means for column C.