Excel index + match with multiple criteria and a particular use of the match mode

Question

I'm trying to combine Excel formulas index and match with multiple criteria, where one of the criteria is an approximate match.

Unlike columns 1 and 2, where I'm looking for an exact match, the match mode argument of the match function - "exact match or next large item" - is needed for the criteria in Column 3.

Here's an example of what I'm trying to do:

Match criteria: A, Y, 185

Expected return: 55.88

I think this is an interesting problem. Will accept answers that don't use index + match as well.

Thank you.

I got this done by using XMATCH, which isn't available in older versions of Excel: =INDEX(Table1[Column 4],XMATCH(185,(Table1[Column 1]="A")*(Table1[Column 2]="Y")*(Table1[Column 3]),1)) — SuavestArt
– SuavestArt, Commented Nov 13, 2021 at 23:49

W_O_L_F · Accepted Answer · 2021-11-15 09:25:51Z

1

XLOOKUP is a powerful new function. The first 1 is referring to the first result.

=XLOOKUP(1,(A2:A17="A")*(B2:B17="Y")*(C2:C17>185),D2:D17)

answered Nov 15, 2021 at 9:25

W_O_L_F

1,5061 gold badge12 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel index + match with multiple criteria and a particular use of the match mode

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related