index and match multple criteria

Question

I am trying to use index and match to find when two columns match another two columns then return another column in the same row. For example,

IF (Sheet1:ColA = Sheet2:ColA) and (Sheet1:ColB = Sheet2:Col B) then return Sheet2:ColC.

Here is what I have so far as my formula which does not work:

=INDEX('Sheet2'!C:C,MATCH(1,('Sheet1'!A1='Sheet2'!A1:A4)*('Sheet1'!B1='Sheet2'!B1:B4),0))

I want the "Wanted Col" values that are highlighted in the image.

Answer 1

Your formula seems correct, but it needs to be an Array formula. To achieve this; edit the formula through the formula bar and then press ctrl+shift+enter to set it as an array formula.

It should then look like this:

{=INDEX('Sheet2'!C:C,MATCH(1,('Sheet1'!A1='Sheet2'!A1:A4)*('Sheet1'!B1='Sheet2'!B1:B4),0))}

Alternatively, you can avoid using array formulas by adding a second index:

=INDEX(Sheet2!C:C;MATCH(1;INDEX((A1=Sheet2!A:A)*(B1=Sheet2!B:B);0;1);0))

Answer 2

If you have Office365 then you can use Filter() formula-

=FILTER(Shee2!$C$1:$C$4,(Shee2!$A$1:$A$4=A1)*(Shee2!$B$1:$B$4=B1))

---

Alternatively You can use INDEX() and SUMPRODUCT() together. Try below-

=INDEX(Sheet2!C:C,SUMPRODUCT(ROW(Sheet2!C:C)*(Sheet2!A:A=A2)*(Sheet2!B:B=B2)))