Comparing two columns, and returning a specific adjacent cell in Excel

Question

I am using a combination of if, vlookup, match, iserror functions, and unfortunately I've not been able to find the right formula.

Comparing two columns for matches is easy enough. the tough part has been returning a specific cell once a match is found.

So what I'm dealing with is something kind of like this:

Header     Column A   Column B   Column C  Column D
Row 1      111        AAA        112
Row 2      222        BBB        111
Row 3      333        CCC        221
Row 4      444        DDD        333

I'm trying to match column values in Column A, with Column C. So if there's match, I want the corresponding value in Column B to populate in Column D. Not a great explanation, but allow me to visually show you what I'm looking for

Header     Column A   Column B   Column C  Column D
Row 2      111        AAA        112
Row 3      222        BBB        111       AAA
Row 4      333        CCC        221
Row 5      444        DDD        333       CCC

Since Cells A1 matches cell C3, I want D to return B2

Same with Row 5. Since A4 and C5 match, I want the value for B5

Let me know if this makes sense or if you need further clarification.

Answer 1

Very similar to this question, and I would suggest the same formula in column D, albeit a few changes to the ranges:

=IFERROR(VLOOKUP(C1, A:B, 2, 0), "")

If you wanted to use match, you'd have to use INDEX as well, like so:

=IFERROR(INDEX(B:B, MATCH(C1, A:A, 0)), "")

but this is really lengthy to me and you need to know how to properly use two functions (or three, if you don't know how IFERROR works)!

Note: =IFERROR() can be a substitute of =IF() and =ISERROR() in some cases :)