Excel: check if Cell value exists in column and then get NEXT Cell value

Question

After checking the availability of the cell value in the column, I need to get the cell value next to the corresponding cell . For example, I check if a value exists in cell A1 in column B and it is assumed that it matches B5 , then I want a 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 to SO , I also managed to get the row number of the corresponding cell:

 =IF(ISERROR(MATCH(A1,B:B, 0)), "No Match", "Match on Row " & MATCH(A1,B:B, 0)) 

So, 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 does not work.

What am I missing? How to add a column number to the row number returned to achieve the desired result?

Answer 1

After t.thielemans answer I worked, just

 =VLOOKUP(A1, B:C, 2, FALSE) 

works fine and does what I wanted, except that it returns #N/A for non-matches; therefore, it is suitable for the case when it is known that the value definitely exists in the search column.

Edit (based on t.thielemans comment):

To avoid #N/A for inconsistencies, do:

 =IFERROR(VLOOKUP(A1, B:C, 2, FALSE), "No Match") 

Answer 2

Use another function, for example VLOOKUP:

 =IF(ISERROR(MATCH(A1,B:B, 0)), "No Match", VLOOKUP(A1,B:C,2,FALSE)) 

Answer 3

How about this?

 =IF(ISERROR(MATCH(A1,B:B, 0)), "No Match", INDIRECT(ADDRESS(MATCH(A1,B:B, 0), 3))) 

“3” at the end means for column C.