Forum Discussion

katrina bethea's avatar
katrina bethea
Brass Contributor
May 23, 2018
Solved

Formula debug. am i using the wrong formula?

I have attached my spreadsheet to help clear up what I am doing.  Basically i want this formula to look at sales/m (sales per month) then check the sale $ (price), and return a buy price based on a c...
Formulas & Functions
Need Help
  • Matt Mickle's avatar
    Matt Mickle
    May 23, 2018

    Katrina-

     

    Try using a lookup table instead it will greatly simplify your formula (I've attached an example file for your reference):

     

    =IF(A2>20,ROUNDUP(B2*VLOOKUP(B2,$K$4:$N$10,2,TRUE),0),ROUNDUP(B2*VLOOKUP(B2,$K$4:$N$10,4,TRUE),0))

     

     

Matt Mickle's avatar
Matt Mickle
Bronze Contributor
May 23, 2018

Katrina-

 

Try using a lookup table instead it will greatly simplify your formula (I've attached an example file for your reference):

 

=IF(A2>20,ROUNDUP(B2*VLOOKUP(B2,$K$4:$N$10,2,TRUE),0),ROUNDUP(B2*VLOOKUP(B2,$K$4:$N$10,4,TRUE),0))

 

 

katrina bethea's avatar
katrina bethea
Brass Contributor
May 23, 2018

yes that works! thank you, never thought about doing it like that. took me a second on how to get it to copy and paste on other excel sheets and still work but i finally got that figured out.

 

**** edit**** i covers all the ranges but some of the values it's returning are wrong but ill see if i cant fix it from here.