On Tue, 28 Oct 2008 12:44:05 -0700, samoan
wrote:

I tried to be as detailed as possible:
I have version 2002 SP3

Can someone provide me a suggestion on formulas for the following:

Scenario - Steve and Joe have red, green, yellow and blue widgets. Each
widget weighs either 1,2,3 or 4 ounces. The price of the widget is based on
all three variables i.e a 1 ounce red widget owned by Steve has a different
cost then a 1 ounce red widget owned by Joe.

So, what I need are formulas that will populate cells with the correct
prices based on these variables. Ex: Steve owns six - 1 ounce red widgets.
Column A contains the name of the owner (Steve / Joe)
Column B contains the color of the widget.
Column C contains the weight of the widget (1,2,3,4 ozs).
Column D contains the number of widgets.

Based on how the formulas could work, I don't mind having separate columns
for the results for the different colors of widgets.
For example, if column E was the column to capture the cost for the red
widgets, the formula should give the answer to: If cloumn A = Steve and if
Column B = red, and if column C = 1, then the result in Column E should be
the amount in column D times $2 (cost of a 1 ounce red widget owned by
Steve), however, if cloumn A = Steve and if Column B = red, and if column C =
2, then the result in Column E should be the amount in column D times $4
(cost of a 2 ounce red widget owned by Steve), however, if cloumn A = Steve
and if Column B = red, and if column C = 3, then the result in Column E
should be the amount in column D times $6 (cost of a 3 ounce red widget owned
by Steve) however, if cloumn A = Steve and if Column B = red, and if column C
= 4, then the result in Column E should be the amount in column D times $8
(cost of a 4 ounce red widget owned by Steve) however If cloumn A = Joe and
if Column B = red, and if column C = 1, then the result in Column E should be
the amount in column D times $3 (cost of a 1 ounce red widget owned by Joe),
however, if cloumn A = Joe and if Column B = red, and if column C = 2, then
the result in Column E should be the amount in column D times $5 (cost of a 2
ounce red widget owned by Joe), however, if cloumn A = Joe and if Column B =
red, and if column C = 3, then the result in Column E should be the amount in
column D times $7 (cost of a 3 ounce red widget owned by Joe) however, if
cloumn A = Joe and if Column B = red, and if column C = 4, then the result in
Column E should be the amount in column D times $9 (cost of a 4 ounce red
widget owned by Joe)
Columns F, G and H could have the same formula but with a different color
for each column. I can sum the columns to get the value by color.

Tx,
Samoan

I don't understand your columns F, G or H

But for Column E:

1. Set up two tables: One for Joe, the other for Steve.

NAME Steve's table "Steve" and Joe's table "Joe".

They should look like this (you've only give info for red widgets, so that's
all I was able to fill in):

1 2 3 4
red $2.00 $4.00 $6.00 $8.00
green
yellow
blue

Where the values for Steve's different weighted; different colored widgets are
properly filled in.

Joe's table would look like:
1 2 3 4
red $3.00 $5.00 $7.00 $9.00
green
yellow
blue

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

Given your values in columns A, B, C, and D, the formula in Column E would be:

=VLOOKUP(B2,INDIRECT(A2),C2+1,FALSE)*D2

The "name" in column A is used by the INDIRECT function to tell VLOOKUP which
table to search.
--ron