CODEWITHSUNDEEP
excel-formulamatch
SuavestArt
SuavestArt

Reputation: 217

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

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.

enter image description here

Upvotes: 0

Views: 640

Answers (1)

W_O_L_F
W_O_L_F

Reputation: 1486

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)

Upvotes: 1

Related Questions