 # Need help with a formula

Occasional Contributor

# Need help with a formula

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

# Re: Need help with a formula

With some assumptions that could be

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

# Re: Need help with a formula

My Excel would not accept it as a formula.

# Re: Need help with a formula

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

# Re: Need help with a formula

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

Thanks! It works!

# Re: Need help with a formula

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.