3 Criteria Lookup in Excel 2021

Hi,
I am looking for result in Sheet1B5:C9 lookup with Sheet2.

Try this,

B5
=INDEX(Sheet2!$A:$Z,MATCH($D$2,Sheet2!$C$1:$C$14,0),SUMPRODUCT((Sheet2!$D$1:$K$1=B$4)*(Sheet2!$D$2:$K$2=$A5)*(COLUMN(Sheet2!$D$1:$K$1))))

copied and across to C8

B9
=SUM(B5:B8)

copied to C9

There is #Spill! Issue in C column.

I guess you have XLOOKUP in Excel 2021, so maybe you try this in B5 and copy across and down:

Formula:

Please Login or Register  to view this content.

Good luck!

B5=SUMPRODUCT((Sheet2!$C$3:$C$14=Sheet1!$D$2)*(Sheet2!$D$2:$K$2=Sheet1!$A5)*(Sheet2!$D$1:$K$1=Sheet1!B$4)*(Sheet2!$D$3:$K$14))

Copy across and down

Total

B9=SUM(B5:B8)

copy across


or for total

B9=SUMPRODUCT((Sheet2!$C$3:$C$14=Sheet1!$D$2)*(ISNUMBER(MATCH(Sheet2!$D$2:$K$2,Sheet1!$A$5:$A$8,0)))*(Sheet2!$D$1:$K$1=Sheet1!B$4)*(Sheet2!$D$3:$K$14))

Copy across

An alternative to post #4, in case you don't have XLOOKUP:

Formula:

Please Login or Register  to view this content.

There is Filter function as well but couldn't work out.

Revise my formula

B2
=IFERROR(INDEX(Sheet2!$A:$Z,MATCH($D$2,Sheet2!$C$1:$C$14,0),1/(1/(SUMPRODUCT((Sheet2!$D$1:$K$1=B$4)*(Sheet2!$D$2:$K$2=$A5)*(COLUMN(Sheet2!$D$1:$K$1)))))),"")

copied down and across C8

B9
=SUM(B5:B8)

copied to C9

In B5:
=IFERROR(FILTER(FILTER(Sheet2!$D$3:$K$13,Sheet2!$C$3:$C$13=$D$2),(Sheet2!$D$1:$K$1=B$4)*(Sheet2!$D$2:$K$2=$A5)),0)

copy across and down.

Thanx all of you.