Need help with a formula

Copper Contributor

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

5 Replies

@Victor_Svensson 

With some assumptions that could be

=LOOKUP(H3,{0,300,400,500},{0,5,7,15})

@Sergei Baklan 

 

My Excel would not accept it as a formula.

@Victor_Svensson 

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

@Sergei Baklan 

 

=LETAUPP(H3;{0\300\400\500};{0\5\7\15}) 

Thanks! It works!

@Victor_Svensson 

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

image.png

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.