Ron,
Your response is helpful except I've never set up a table before, so don't
know how to name it and make the vlookup formula find the table. I also want
to keep the totals for the cost in separate colmns by color. Ex:
Here's the raw data
A B C D
1 Steve Red 2 200
2 Joe Blue 4 14
3 Steve Yellow 3 500

The calculations (Cost) needs to reside in the next set of columns (for the
ex above) ( Note the column headings) Note the price for a Blue 4 oz widget
for Joe is $40 each and the price for a Yellow 3 oz widget for Steve is $16
each )

Red widgets Green widgets Yellow Widgets Blue Widgets
E F G
H
1 $800.00 False (or blank) False (or blank) False (or
blank)
2 False (or blank) False (or blank) False (or blank) $64.00
3 False (or blank) False (or blank) $8,000.00 False (or
blank)

Does the VLOOKUP formula get copied into each of the cells I want the
results in, as in cells e1.h3?
Where do I put the tables (Steve, Joe) in the spreadsheet and how do I
reference them (you may have to resist telling me to take an excel class)?

--
Tx,
Samoan


"Ron Rosenfeld" wrote:

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