CODEWITHSUNDEEP
excelindexingmatch
SSS
SSS

Reputation: 2442

index and match multple criteria

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,

enter image description here

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.

Upvotes: 1

Views: 84

Answers (2)

Harun24hr
Harun24hr

Reputation: 36770

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)))

enter image description here

Upvotes: 0

Slaqr
Slaqr

Reputation: 563

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))

Upvotes: 1

Related Questions