Forum Discussion

Victor_Svensson's avatar
Victor_Svensson
Copper Contributor
Dec 14, 2020

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...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 14, 2020

Victor_Svensson 

With some assumptions that could be

=LOOKUP(H3,{0,300,400,500},{0,5,7,15})
    • Victor_Svensson's avatar
      Victor_Svensson
      Copper Contributor
      Dec 14, 2020

      SergeiBaklan 

       

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

      Thanks! It works!

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Dec 14, 2020

        Victor_Svensson 

        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.

         

Resources