Compare two columns and return a specific neighboring cell in Excel

Question

I use a combination of if , vlookup , match , iserror , and, unfortunately, I could not find the correct formula.

Comparing two columns for matches is quite simple. the hard part returned a specific cell after a match was found.

So I'm dealing with something 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 am trying to match column values ​​in Column A , with Column C Therefore, if this matches, I want the corresponding value in Column B populate in Column D Not a great explanation, but let me 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 correspond to cell C3 , I want D return B2

Same thing with line 5 . Since A4 and C5 consistent, I need a value for B5

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

Answer 1

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

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

If you want to use a match, you will also need to use INDEX , for example:

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

but for me it is a lot, and you need to know how to use two functions (or three if you do not know how IFERROR works)!

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

Answer 2

Here's what to do in D1: =VLOOKUP(C1, $A$1:$B$4, 2, FALSE)

You can then copy this to the rest of column D.

Answer 3

In cell D2 and copied down:

 =IF(COUNTIF($A$2:$A$5,C2)=0,"",VLOOKUP(C2,$A$2:$B$5,2,FALSE)) 

Answer 4

I would advise you to swap columns B and C for the reason that I will explain. Then in D2 type: = VLOOKUP (A2, B2: C4, 2, FALSE)

Finally, copy the formula for the remaining cells.

Explanation: VLOOKUP will first find the value A2 in the range B2 to C4 (second argument). NOTE: VLOOKUP always searches for the first column in this range. That's why you need to swap two columns before doing anything.

Once an exact match is found, it will return the value in the adjacent cell (third argument).

This means that if you put 1 as the third argument, the function will return the value in the first column of the range (which will be the same value you were looking for). If you put 2, it will return the value from the second column in the range (the value in the next cell is the RIGHT SIDE of the found value).

FALSE indicates that you are finding an exact match. If you set to TRUE, you will look for an approximate match.