Need help with a formula

Question

I try to make a formula where I get a specific number if another cell is within a certain range.

I tried many different solutions but no success.

Give it in text

IF H3 is in the range 0-299 = 0
If H3 is in the range 300-399 = 5
If H3 is in the range 400-499 = 7
If H3 is within the range 499 <= 15

sergeibaklan · Answer

Victor_Svensson&nbsp;
With some assumptions that could be
=LOOKUP(H3,{0,300,400,500},{0,5,7,15})

victor_svensson · Answer

SergeiBaklan&nbsp;&nbsp;My Excel would not accept it as a formula.

sergeibaklan · Answer

Victor_Svensson&nbsp;
Please open attached workbook and check within it how the formula looks for your locale.

victor_svensson · Answer

SergeiBaklan&nbsp;&nbsp;=LETAUPP(H3;{0\300\400\500};{0\5\7\15})&nbsp;Thanks! It works!

sergeibaklan · Answer

Victor_Svensson&nbsp;
Great. But even better not to hardcode constants within formula, but use helper range at any place of your workbook, like

Formulae could be
=XLOOKUP(H3,$L$2:$L$5,$M$2:$M$5,,-1)

or

=VLOOKUP(H3,$L$2:$L$5,2)

or like
Above formulas are for English locale, if open attached file it will be transferred into your locale.
&nbsp;

Forum Discussion