Excel vlookup 3 columns from sheet A and match with the two columns in sheet B, and give 3rd column in sheet B

Question

I would like to do a vlookup or any function to match data in two sheets (sheet A and sheet B).

This is my sheet A:

This is my sheet B (Imagine the column is A, B, C, instead of E,F,G in the image):

I want the answer in sheet B, column C. E.g. the result should be like below.

I tested the function below, but not working.

=VLOOKUP($A1+$B1,SheetA!$A:$C,3,FALSE)

yeah. I'm able to do vlookup for 1 column, but not matching two criteria. — Edwin Eddie, Sep 20 '18 at 21:36
`=INDEX(SheetA!$C$3:$C$6, MATCH(1, (SheetB!E3 = SheetA!$A$3:$A$6) * (SheetB!F3 = SheetA!$B$3:$B$6),0))` and press CTRL+SHIFT+ENTER after typing into formula bar, because it's an array formula. — Mako212, Sep 20 '18 at 21:42
These will only work exactly as written if your sheet names are exactly as you indicated, and your data matches your images, with data starting in row 1 in both sheets. — Mako212, Sep 20 '18 at 21:51

score 0 · Answer 1 · answered Sep 20 '18 at 21:56

You can use an array formula version of INDEX/MATCH to match on multiple criteria (you must press CTRL+SHIFT+ENTER after typing it in the formula bar to make it an array formula):

=INDEX(SheetA!$C$3:$C$6, MATCH(1, (SheetB!E3 = SheetA!$A$3:$A$6) * (SheetB!F3 = SheetA!$B$3:$B$6),0))

Each set of criteria goes in parenthesis within MATCH, separated by *, with the value on the left, and the range to match from on the right of the = sign.

Excel vlookup 3 columns from sheet A and match with the two columns in sheet B, and give 3rd column in sheet B

1 Answers1