Index match with 3 criteria

[babychai]

Index Match v1.png

Hi,

Does Index Match and Xlookup support 3 criteria?
In cell E10 i try Xlookup for 3 criteria but come out with an error. Can assist to fix it with Xlookup and Index Match formula? Please note that one of the Match Mode i using is -1 because i looking the date is below 30/3/2020. The result expected is 98.
What else formula if both does not support.

Thanks dude.

[Bo_Ry]

Please try
=XLOOKUP(B12,C3:G3,XLOOKUP(A4&B4,A4:A6&B4:B6,C4:G6),,-1)

or
=XLOOKUP(B12,C3:G3,FILTER(C4:G6,(A4:A6=B11)*(B4:B6=B10)),,-1)

[Fluff13]

Or with index match
=INDEX($C$4:$G$6,MATCH(B11&"|"&B10,$A$4:$A$6&"|"&$B$4:$B$6,0),MATCH(TRUE,(B12>=$C$3:$G$3),0))

Needs CSE entry

[RaulSerg]

Where exactly is the third dimension of criteria since A4:A6 and B4:B6 are uniquely associated with each other?

e.g.: Criteria1 = haha and Criteria2 = medium should yield what result in your data set?

[babychai]

Please try
=XLOOKUP(B12,C3:G3,XLOOKUP(A4&B4,A4:A6&B4:B6,C4:G6),,-1)

or
=XLOOKUP(B12,C3:G3,FILTER(C4:G6,(A4:A6=B11)*(B4:B6=B10)),,-1)

Ah, thank you

Or with index match
=INDEX($C$4:$G$6,MATCH(B11&"|"&B10,$A$4:$A$6&"|"&$B$4:$B$6,0),MATCH(TRUE,(B12>=$C$3:$G$3),0))

Needs CSE entry

Index Match v2.png

hi, it's work. btw, i wondering, the formula consisting of 2 Area won't with different set of criteria (A4:B6 and A7:B10) ?

Where exactly is the third dimension of criteria since A4:A6 and B4:B6 are uniquely associated with each other?

e.g.: Criteria1 = haha and Criteria2 = medium should yield what result in your data set?

Yes, there's some mistake in the Criteria. i already fixed it and the solution provided by forumer able to solved my issue with the correct criteria.

[Fluff13]

It needs to be like
=INDEX($C$4:$G$10,MATCH(B14&"|"&B13,$A$4:$A$10&"|"&$B$4:$B$10,0),MATCH(TRUE,(B15>=$C$3:$G$3),0))

and the xlookup
=XLOOKUP(B13,B4:B10,XLOOKUP(B15,C3:G3,C4:G10,,-1),,0)

[RaulSerg]

and the xlookup
=XLOOKUP(B13,B4:B10,XLOOKUP(B15,C3:G3,C4:G10,,-1),,0)

This one's incomplete/not working.
It would need to be amended as suggested above to: =XLOOKUP(B13&B14,B4:B10&A4:A10,XLOOKUP(B15,C3:G3,C4:G10,,-1),,0)

Regards

[Fluff13]

I left the formula looking at two criteria, because that is how it was listed.

[babychai]

Fluff13 and RaulSerg, thank you so much~! ^_^

[Czeslaw]

See file with formulas

1.

Please Login or Register  to view this content.

2.

Please Login or Register  to view this content.

[Fluff13]

Fluff13 and RaulSerg, thank you so much~! ^_^

You're welcome & thanks for the feedback

[babychai]

See file with formulas

1.

Please Login or Register  to view this content.

2.

Please Login or Register  to view this content.

Hello, not really work. the date criteria should set lower/less then the date array. i do changed to "lesser than" by myself but then not working.
anyway, thanks for trying to help