# Formula Recommendation

#### stockopt

##### New Member
I have referred to a number of your videos and your advice has helped me immensely. Can you please proved a recommendation for me? I am putting together an order form and to set up the shipping and handling. Here are the guidelines:

 Total Shipping \$0.00 \$10.00 \$99.99 \$10.00 \$100.00 10.00% \$1,999.99 10.00% \$2,000.00 9.50% \$4,999.90 9.50% \$5,000.00 \$0.00

<colgroup class=""><col width="101" class="" style="width: 76pt;"><col width="141" class="" style="width: 106pt;"></colgroup><tbody class="">
</tbody>

I have a cell with the subtotal where I have used the Vlookup but the problem is with orders below \$100. Can I do a Sumif and Vlookup combo? With the Vllookup, I have to multiply Shipping (%) with subtotal but I am unable to add \$10 for all orders under \$100. Thank you for your time.

#### etaf

##### Well-known Member

IF( subtotal < \$100.00 , Subtotal + 10 , Vlookup () )

Thank you.

#### etaf

##### Well-known Member
did that solve the question for you

#### stockopt

##### New Member
Wayne,

I wrote this formula: IF(subtotal <100,subtotal +10,VLOOKUP(subtotal ,Shipping,2)*subtotal ). If automatically add the the \$10 but will the amount if over \$100 it also the \$10. It this correct formula. Thank you again

#### stockopt

##### New Member
I also have the cell formatted for % (subtotal)

#### taikvei

##### New Member
Wayne,

I wrote this formula: IF(subtotal <100,subtotal +10,VLOOKUP(subtotal ,Shipping,2)*subtotal ). If automatically add the the \$10 but will the amount if over \$100 it also the \$10. It this correct formula. Thank you again
VLOOKUP(subtotal,shipping,2)*subtotal will only give you the shipping \$, if you want the amount + shipping, you need subtotal + vlookup(subtotal,shipping,2)*subtotal.

Add ,1 at the end of your vlookup to make it vlookup(subtotal,shipping,2,1)
this will make it an approximate match, rather than an exact match

Last edited: