Need help with a formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1985183%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1985183%22%20slang%3D%22en-US%22%3E%3CP%3EI%20try%20to%20make%20a%20formula%20where%20I%20get%20a%20specific%20number%20if%20another%20cell%20is%20within%20a%20certain%20range.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20many%20different%20solutions%20but%20no%20success.%3C%2FP%3E%3CP%3EGive%20it%20in%20text%3C%2FP%3E%3CP%3EIF%20H3%20is%20in%20the%20range%200-299%20%3D%200%3CBR%20%2F%3EIf%20H3%20is%20in%20the%20range%20300-399%20%3D%205%3CBR%20%2F%3EIf%20H3%20is%20in%20the%20range%20400-499%20%3D%207%3CBR%20%2F%3EIf%20H3%20is%20within%20the%20range%20499%20%26lt%3B%3D%2015%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1985183%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1985225%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1985225%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F901121%22%20target%3D%22_blank%22%3E%40Victor_Svensson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20some%20assumptions%20that%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLOOKUP(H3%2C%7B0%2C300%2C400%2C500%7D%2C%7B0%2C5%2C7%2C15%7D)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1985227%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1985227%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20Excel%20would%20not%20accept%20it%20as%20a%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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.