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)
See also the following modified sample file: MatchIntoRange.ods
Note(s):
1 since the formula does no lookup but calculates an index only.=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 