Check if Cell value exists in Column and then get the value of the NEXT Cell

Question

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    amp  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 amp 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

User · Answer

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

User · Answer

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

User · Answer

Use a different function  like VLOOKUP    IF ISERROR MATCH A1 B B  0     No Match   VLOOKUP A1 B C 2 FALSE

[excel] Check if Cell value exists in Column, and then get the value of the NEXT Cell

Examples related to excel