Match and Find Matrix Values with Criteria

Need a formula that searches a matrix, locates a range associated with two input values and then finds the nearst value to a third given input (eg 2.6) THAT is not higher than this value (2.6). "Black" and "cheese" in attached example correlate with the "range" values 1, 2, 3 and 12). The expected result would be 2 given the three inputs because 2 is the closest number to 2.6 that is not above 2.6…..help is MUCH APPRECIATED. Very important project here….

dreicer_Jarr,

Using your example workbook, this formula should work:

Please Login or Register  to view this content.

Try this "array formula"

=MAX(IF(B9:C9=C6,IF(A10:A16=C5,IF(B10:C16<=C4,B10:C16))))

confirmed with CTRL+SHIFT+ENTER

PERFECT! It look me about ten minutes because I needed to apply the simplified example to the more complicated model, but it works perfect! You are an Excel genious! Thanks again.

dreicer_Jarr,

On the chance that you're talking about the formula I provided (which I'm assuming because what I provided is long and would probably take about 10 min to adjust, lol), then you're welcome

However, I feel that it should be stated that DLL's formula will work better than mine because it has fewer function calls and mine calculates like an array formula anyway ^_^

They both work great. Must say DaddyLongLegs solve was much easier to implement --- though I hear you should avoid array formulas.

Originally Posted by dreicer_Jarr

....though I hear you should avoid array formulas.....

In this situation I would think that it's the most efficient way

You can alter my suggestion so that it doesn't require CSE, i.e. this version

=MAX(INDEX((B9:C9=C6)*(A10:A16=C5)*(B10:C16<=C4)*B10:C16,0))

[assuming you don't ever expect the result to be a negative number]

....but actually the multiplication in that probably makes it marginally less efficient than using my previous suggestion with IFs