CODEWITHSUNDEEP
excelexcel-2007excel-formula
Jay
Jay

Reputation: 751

How can I get values from an adjacent cell if there is a match between two columns?

I have the following table

A        B        C        D       E
cat1     mary              sam
boy      steve             jenny
cashier  scott             mary
cook     andrew            scott
soldier  paul              mary

Column B contains reference values. Column D contains values I'd like to compare to those in column B. If value in Column D matches with one of the values in Column B, then I'd like the adjacent value (Column A) of the match to show up in Column C.

A        B        C        D       E
cat1     mary              sam
boy      steve             jenny
cashier  scott    cat1     mary
cook     andrew   soldier  scott
soldier  paul     cat1     mary

                           sarah
                  boy      steve

The following is the best I can come up with but the values seem to be shifted. How do I call for the value in the adjacent cell of the matching reference value from Column B? 

=IF(ISNA(MATCH(D1,$B:$B,0)),"",$A:$A)

Any assistance is much appreciated. Thank you.

edit: GOT IT! =IFERROR(INDEX(A:A, MATCH(D1,B:B, 0)), "")

Upvotes: 0

Views: 2332

Answers (1)

Kamal G
Kamal G

Reputation: 278

Try this =IFERROR(INDEX($A:$A,MATCH(D1,$B:$B,0),1),"")

Upvotes: 1

Related Questions