Look up formula

[Ruth]

Hi

We have a worksheet that lists the customer, load and unload ports and the
rate that we apply for that voyage. We have another worksheet where we are
calculating the revenue for a voyage, by looking up the rate by matching the
customer, load and unload ports to get the rate and multipying the rate by
the tonnage and dividing it by the time. We used the following formula:


=IF($D8>0,(LOOKUP(3,3/($D8=Rates!$A$7:$A$4000)*($F8=Rates!$G$7:$G$4000)*($H8=Rates!$J$7:$J$4000),(Rates!$N$7:$N$4000*($J8+$M8))/$AQ8))," ")

But it is grabbing the last rate for the customer and not bothering to look
up and match the load and unload ports. We do not know what the "3,3" is for
in the formula and may be using it incorrectly.

Please let us know the correct formula to use.

 

[T. Valko]

Try this:

=IF($D8>0,SUMPRODUCT(--(Rates!$A$7:$A$4000=$D8),--(Rates!$G$7:$G$4000=$F8),--(Rates!$J$7:$J$4000=$H8),Rates!$N$7:$N$4000)*($J8+$M8)/$AQ8,"")

 

[Ruth]

The formula did not work, do you have any other suggestions?

--
Thank-you!
Ruth

Try this:

=IF($D8>0,SUMPRODUCT(--(Rates!$A$7:$A$4000=$D8),--(Rates!$G$7:$G$4000=$F8),--(Rates!$J$7:$J$4000=$H8),Rates!$N$7:$N$4000)*($J8+$M8)/$AQ8,"")

 

[T. Valko]

The formula did not work

What does that mean? Incorrect result? Error? Computer locked up? Monitor
started spewing smoke?

Try this array formula** :

=IF($D8>0,INDEX(Rates!$N$7:$N$4000,MATCH(1,(Rates!$A$7:$A$4000=$D8)*(Rates!$G$7:$G$4000=$F8)*(Rates!$J$7:$J$4000=$H8),0))*($J8+$M8)/$AQ8,"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP

The formula did not work, do you have any other suggestions?