Index Match Formula with If Statement

[lorber123]

I am working on a problem and am having issues on this last piece. The following is the scenario.

I am trying to calculate shipping costs. I need to Use Index and Match formula in the Shipping Prices tab by using the shipping zone in cell B20 and the total amount of the order seen in cell E37.



i. Tier 1: Orders of $0-$99.99:

ii. Tier 2: $100-$199.99:

iii. Tier 3:

iv. In addition, if the customer orders $250 or more worth of product (before tax and shipping), then shipping is free. Free shipping should be shown with the word ‘FREE’ in the shipping cost output cell.

v. If the customer lives in Alaska or Hawaii, additional handling charges of $15 should be added to the standard shipping fee (and should apply even if shipping is free).

[Pete_UK]

Put this formula in E39:

=IF(OR(B15="Hawaii",B15="Alaska"),15,0)+IF(E37>250,0,INDEX('Shipping Pricing'!$B$3:$E$5,MATCH(E37,'Shipping Pricing'!$A$3:$A$5),MATCH(VLOOKUP(B15,'Shipping Zones by State'!A:B,2,0),'Shipping Pricing'!$B$2:$E$2,0)))

Apply a Custom format to that cell of:

General;;"FREE"

to show zero values as the word FREE.

Hope this helps.

Pete