-1

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)
Djib2011
  • 6,874
  • 5
  • 36
  • 41
Edwin Eddie
  • 11
  • 1
  • 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
  • =VLOOKUP(SheetB!$A3, SheetA!$A$3:$A$6,3,0) <- not working for me. – Edwin Eddie Sep 20 '18 at 21:48
  • 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
  • Mako212. Yeah! The index works! thanks! – Edwin Eddie Sep 20 '18 at 21:53

1 Answers1

0

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.

Mako212
  • 6,787
  • 1
  • 18
  • 37