Excel formula help for calculating freight.

[roland_arv]

Hello everyone,

I need help with a formula please. Spreadsheet is attached in zip file.


The attached spreadsheet has (4) sheets. The first sheet contains th
formulas, the 2nd through 4th sheets contain freight costs/weights for
different freight companies. Columns H, I, and L already have activ
formulas within them. Column M is where I need the freight formula.

------------

This is what I would like to have happen (if possible) in Column M unde
Shipping Cost but can't seem to figure it out. Here are my scenarios:

1. If Column H and Column I show "Eligible" or if Column H show
"Eligible" and Column I shows "NonEligible", use data from ShipMethod
Freight sheet and depending on what Column L is showing as th
"Calculated Weight" value, pull the corresponding freight cost fro
ShipMethod1 Freight sheet into Column M. (Note: The issue here is tha
Calculated Weight is not always a whole number so the calculation has t
look at weight ranges on the freight table to choose the righ
associated cost. So for example, if the Calculated Weight was .35, th
formula should choose the freight cost value of $2.09 from ShipMethod
Freight sheet.)

2. If Column H shows "NonEligible" and Column I shows "Eligible", us
data from ShipMethod2 Freight sheet and depending on what Column L i
showing as the "Calculated Weight" value, pull the corresponding freigh
cost from ShipMethod2 Freight sheet into Column M. (Note: Same issu
here with ranges...)

3. If Column H shows "NonEligible" and Column I shows "NonEligible", us
data from ShipMethod3 Freight sheet and depending on what Column L i
showing as the "Calculated Weight" value, pull the corresponding freigh
cost from ShipMethod3 Freight sheet into Column M. (Note: Same issu
here with ranges...)
------------

I hope this makes sense...

Thank you for any and all help

+-------------------------------------------------------------------
|Filename: test_073113.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=896
+-------------------------------------------------------------------

 

[Claus Busch]

Hi Roland,

Am Thu, 1 Aug 2013 20:29:51 +0100 schrieb roland_arv:

1. If Column H and Column I show "Eligible" or if Column H shows
"Eligible" and Column I shows "NonEligible", use data from ShipMethod1
Freight sheet and depending on what Column L is showing as the
"Calculated Weight" value, pull the corresponding freight cost from
ShipMethod1 Freight sheet into Column M. (Note: The issue here is that
Calculated Weight is not always a whole number so the calculation has to
look at weight ranges on the freight table to choose the right
associated cost. So for example, if the Calculated Weight was .35, the
formula should choose the freight cost value of $2.09 from ShipMethod1
Freight sheet.)

2. If Column H shows "NonEligible" and Column I shows "Eligible", use
data from ShipMethod2 Freight sheet and depending on what Column L is
showing as the "Calculated Weight" value, pull the corresponding freight
cost from ShipMethod2 Freight sheet into Column M. (Note: Same issue
here with ranges...)

3. If Column H shows "NonEligible" and Column I shows "NonEligible", use
data from ShipMethod3 Freight sheet and depending on what Column L is
showing as the "Calculated Weight" value, pull the corresponding freight
cost from ShipMethod3 Freight sheet into Column M. (Note: Same issue
here with ranges...)

try in M2:
=INDEX('ShipMethod1 Freight'!B2:B151,MATCH(L2,'ShipMethod1 Freight'!A2:A151,1))*(H2="Eligible")+INDEX('ShipMethod2 Freight'!B2:B151,MATCH(L2,'ShipMethod2 Freight'!A2:A151,1))*(AND(H2="NonEligible",I2="Eligible"))+INDEX('ShipMethod3 Freight'!B2:B151,MATCH(L2,'ShipMethod3 Freight'!A2:A151,1))*(AND(H2="NonEligible",I2="NonEligible"))


Regards
Claus B.

 

[roland_arv]

Hello Claus,

Thank you so much for looking at this. Unfortunately, it did not wor
for us. We actually did a work-around that seems to be working at thi
time. Thank you again.

Hi Roland,

Am Thu, 1 Aug 2013 20:29:51 +0100 schrieb roland_arv:
-

try in M2:
=INDEX('ShipMethod1 Freight'!B2:B151,MATCH(L2,'ShipMethod
Freight'!A2:A151,1))*(H2="Eligible")+INDEX('ShipMethod
Freight'!B2:B151,MATCH(L2,'ShipMethod
Freight'!A2:A151,1))*(AND(H2="NonEligible",I2="Eligible"))+INDEX('ShipMethod
Freight'!B2:B151,MATCH(L2,'ShipMethod
Freight'!A2:A151,1))*(AND(H2="NonEligible",I2="NonEligible"))


Regards
Claus B.

+-------------------------------------------------------------------
+-------------------------------------------------------------------