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($D80,(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.

--
Thank-you
Ruth

Try this:

=IF($D80,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,"")

--
Biff
Microsoft Excel MVP


"Ruth" wrote in message
...
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($D80,(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.

--
Thank-you
Ruth

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

--
Thank-you!
Ruth


"T. Valko" wrote:

Try this:

=IF($D80,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,"")

--
Biff
Microsoft Excel MVP


"Ruth" wrote in message
...
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($D80,(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.

--
Thank-you
Ruth

The formula did not work

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

Try this array formula** :

=IF($D80,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


"Ruth" wrote in message
...
The formula did not work, do you have any other suggestions?

--
Thank-you!
Ruth


"T. Valko" wrote:

Try this:

=IF($D80,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,"")

--
Biff
Microsoft Excel MVP


"Ruth" wrote in message
...
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($D80,(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.

--
Thank-you
Ruth