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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

In cell D2 and copied down    IF COUNTIF  A 2  A 5 C2  0    VLOOKUP C2  A 2  B 5 2 FALSE

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

Examples related to excel

Examples related to vlookup

Examples related to formulas

Examples related to excel-match