MATCH multiple criteria then select the smallest value >= the lookup value

[BaldEagle199]

I am trying to use OFFSET with INDEX/MATCH to MATCH on 2 criteria:'IsPro?' and 'Price Schedule', then find the smallest 'Break-even Txns' that is >= my lookup value. I have been successful retrieving other data cells using OFFSET/INDEX/MATCH, but I am having trouble finding the smallest break-even txns >= my lookup value.

So, assume I am looking up a Basic SDK account with projected transactions of 19,704, it should return 'Tier' = 14,400.

Tiers.PNG

[JBeaucaire]

Post a workbook with some sample data and I'll try it. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

[BaldEagle199]

Thanks for responding. Here is some sample data. Again, I am comfortable with the OFFSET, INDEX and MATCH functions, but once I match on IsPro and Price Schedule, I cannot find the smallest BreakEven Price that is >= the lookup value. I have tried using Match Type = -1, but it only returns #N/A.

[JBeaucaire]

No attachment seen. Try again.

[BaldEagle199]

Sample data attached

[JBeaucaire]

1) Sort the table by col A ascending, col B Asc, col C Asc.
2) Then this monster OFFSET will work in D86:

=INDEX(OFFSET($A$1, MATCH(A86&B86, INDEX($A$1:$A$81&$B$1:$B$81, 0), 0)-1, 2, COUNTIFS($A$1:$A$81, A86, $B$1:$B$81, B86), ), MATCH(C86, OFFSET($A$1, MATCH(A86&B86, INDEX($A$1:$A$81&$B$1:$B$81, 0), 0)-1, 2, COUNTIFS($A$1:$A$81, A86, $B$1:$B$81, B86), ), 1))


If you want to see a simpler break down, look over in K86 and L86. These are the parts that solve specific parts of the puzzle. The formula in M86 is MUCH easier to read usig those references. The big formula in D86 has simply subsituted the references to K86 and L86 with the actual formulas in those cells.

[singapore]

Hi,

You can look at the attached file. First I wrote the formula in the cell E2 and copied it down. Then I wrote the formula in the cell H1. It works correctly.

[Marcol]

The simplest way is as, singapore suggests, use a helper column.

Sheet1 Uses a helper column, Column H. (Hidden with the +/- grouping button)
The result is in L1, and is non-volatile.

Sheet2 Offers a CSE array based solution.
Option 1, in $I$3 is an alternative to Jerrys' "monster", It's even longer, but it works in all versions of Excel.
The other options use helper cells to cut down the formula size, and should help you to understand the "Big Yin"

Note
Neither sheet needs sorting.

I'd go with Sheet1. --- ...

[BaldEagle199]

JBeaucaire, thanks for your response. Your formula works some of the time. If I enter lookup values = Basic, SDK, 650, your formula returns tier 360, but it should be 720.

[JBeaucaire]

your parameters indicated you wanted the value below the numeric, not above.

[Marcol]

Have you tried the attachment in post #8?

Both sheets seem to return the answer you expect for

Please Login or Register  to view this content.

[BaldEagle199]

Yes, I tried Sheet1 and it works well, thanks. I do not understand what is happening in the helper column, and in the lookup formula I do understand what SMALL(F:F,1) does. I have applied your formula to my spreadsheet and adjusted the cell references, etc., and I cannot figure out what I did wrong. I am getting #NUM as a result. When I debug the formula, the SMALL function appears to be the problem. Sorry, I cannot share more of my spreadsheet due to confidential info. Thanks for your help.

[singapore]

In the post # 8, in Sheet 1, the formula in the helper column is really better than my previous suggestion. So I revised and shortened my previous formula, it seems to work correctly for all lookup values.