93

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?

Braiam
  • 1
  • 11
  • 47
  • 78
SNag
  • 17,681
  • 10
  • 54
  • 69

3 Answers3

102

Use a different function, like VLOOKUP:

=IF(ISERROR(MATCH(A1,B:B, 0)), "No Match", VLOOKUP(A1,B:C,2,FALSE))
CustomX
  • 9,948
  • 30
  • 85
  • 115
  • 1
    Thanks! This works too! And I think it'd be better to use `VLOOKUP(A1, B:C, 2, FALSE)` instead of using a fixed range (so as to accommodate a growing look-up array)? – SNag Oct 16 '12 at 13:39
  • 1
    Yeah, just used this fixed range as a test. Edited it in my answer. – CustomX Oct 16 '12 at 13:45
  • You don't need `IFERROR` here. It works fine without it, because the VLOOKUP is only executed if there is a match. – SNag Oct 16 '12 at 14:04
  • Oops! I seem to have been testing in the wrong column :P Indeed without IFERROR it works perfectly too :P – CustomX Oct 16 '12 at 14:05
  • 4
    This is older than old, but I just wanted to say that I've been using this for years and it really bugs me that you were never given best answer. I love that it makes the table scalable for comparing multiple columns. – DuffDuff Dec 15 '15 at 18:23
  • Can anyone tell me how can I apply this formula in excel? I mean in which cell which and how. – ARUN Feb 29 '16 at 08:06
  • You put it in any cell you like, depends on your layout – CustomX Feb 29 '16 at 08:25
  • The parameter in the VLOOKUP function where it says 2, how can I copy cell content 4 cells away? Since 2 seems to work if you'll copy the next cell. I tried looking for another solution, but can't find one and putting other number aside from 2 doesn't work. Hope you could help. – kmf Sep 27 '16 at 16:36
40

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")
Community
  • 1
  • 1
SNag
  • 17,681
  • 10
  • 54
  • 69
  • 3
    Use this instead `IFERROR(VLOOKUP(A1, B:C, 2, FALSE),0)`. I also added this to my answer. 0 being whatever value you want :) (use this in the code will lead to No Match if no match is found ;) – CustomX Oct 16 '12 at 13:53
8

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.

Vincent Tan
  • 3,058
  • 22
  • 21
  • 2
    Fantastic! I just tried exactly that (based on [Excel: get content of a cell given the row and column numbers](http://stackoverflow.com/q/4813888/979621)), and was about to answer my own question, but you beat me to it! Thanks, it works great! – SNag Oct 16 '12 at 13:27