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.

why VLOOKUP is not working?? – Georges Brisset Aug 12 '13 at 16:23 — Georges Brisset, Aug 12 '13 at 16:23

score 8 · Accepted Answer · edited May 23 '17 at 12:06

8

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 :)

edited May 23 '17 at 12:06

Community

1
1

answered Aug 12 '13 at 16:43

Jerry

70,495
13
100
144

+1 for teaching me the columns selector in VLOOKUP - thx - I like using IFERROR as well, makes the sheet neat. – Georges Brisset Aug 12 '13 at 16:48
Hi if in the same senario if i want whole row data till colum b what ever mathcng then how to modify the above formula – Mallik Oct 25 '13 at 12:34
@user1345837 You want them combined or in two columns? You'll need more than one formula for the second case. – Jerry Oct 27 '13 at 15:36

score 1 · Answer 2 · answered Aug 12 '13 at 16:23

1

Here is what needs to go in D1: =VLOOKUP(C1, $A$1:$B$4, 2, FALSE)

You should then be able to copy this down to the rest of column D.

answered Aug 12 '13 at 16:23

tonyj444

383
4
13

Now, what I'm trying to do is match Columns values in column A, with Column E. So if there's match, I want the corresponding value in Column B,C,D to populate in Column F,G,H. How to do this ?? – vinita Nov 11 '13 at 08:17

score 1 · Answer 3 · answered Dec 28 '15 at 10:49

I would advise you to swap B and C columns 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 of A2 in the range B2 to C4 (second argument). NOTE: VLOOKUP always searches the first column in this range. This is the reason why you have to swap the two columns before doing anything.

Once the 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 adjacent cell-RIGHT SIDE of the found value).

FALSE indicates that you are finding the exact match. If you put TRUE, you will be searching for the approximate match.

This was very helpful for a similar situation I have. Well laid out. However, I'm noticing that it doesn't work for very LONG lists where I'm comparing data in a long list (100k+ rows) to a shorter array (47 rows). Once the comparison gets past the 47th row I just get #N/A — Roderic Langer, Feb 11 '21 at 14:59
I would need more details to know how to assist you. But if you want to fix the short array to avoid the #N/A when you use the fill handle, press F4 just after typing the addresses of that array. — DK250, Feb 19 '21 at 13:53

score 0 · Answer 4 · answered Aug 12 '13 at 16:28

0

In cell D2 and copied down:

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

answered Aug 12 '13 at 16:28

tigeravatar

26,199
5
30
38

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

4 Answers4

Linked

Related