How to Find a Result Based on Ranges

I’m trying to figure out the formula to use that will return a specific result based on ranges of prices.

In the attached example, I need to know the level number based on the square foot price. If someone types a square foot price into the yellow cell, it should return the relevant level in the red cell.

Sample Spreadsheet.ods

Example: something that is $27.63 per square foot is “Level 4” (because it falls within the range of $24.00 to $27.99.


you may use the following formula:


See also the following modified sample file: MatchIntoRange.ods


  • This only works, if your levels are consecutive integers starting from 1 since the formula does no lookup but calculates an index only.
  • A more general solution: =SUMPRODUCT(($F$7=>B3:B12)*($F7<=C3:C12)*(A3:A12)) requires integers only in column LEVEL

Update [according to comment of OP]
Here is a new modified version: MatchIntoRange.ods

Hope that helps.

If the answer is correct or helped you to solve your problem, please click the check mark (:heavy_check_mark:) next to the answer.

This formula works unless I select an exact number listed in the range. Example: $15.00 returns a result of "#N/A.

yepp - but be honest; that’s a simple adaption ($F$7=>B3:B12) - see my updated file.

Simple for you, not for me. LOL.

These both work great! Thanks so much for your help!

Is there a reason why I might want to use one over the other? Does it matter?

I’d tend to recommend the more general approach - just make an experiment an rename one level in the midth of levels to an number not in sequence - (e.g rename level 6 to 21 ), select a price in the range and you’ll see, why I recommend the second formula.

Makes sense now. Thanks again!

I’d simply removed column C, and swapped columns A and B, so that A were range starts, and B - levels. When A is sorted, simple


would do the trick - actually the “range lookup in sorted array” mode (the default) is specifically intended for this task.

That would be my way too, but didn’t dare to propose a layout change.

@anon73440385: your answer fits the question brilliantly. Yet, I suppose that it is worth to also provide an alternative way - may it be that OP has simply not known that the layout is not optimal, and some small changes may simplify things drastically :slight_smile: