 # 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.

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

Hello,

you may use the following formula:

`=MATCH(1;(\$F\$7=>B3:B12)*(\$F7<=C3:C12);0)`

Note(s):

• 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 ( ) 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

``````=VLOOKUP(F7;A3:B12;2)
``````

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 