Need help solving this problem

**Stan T** · 10-25-2006, 11:17 AM

I'm going nuts trying to figure out this problem. I am trying to develop a simple costing sheet for salespeople. I need for a salesperson to be able to enter two variables to find the proper price, those variables are color and thickness. there are three colors - natural, white, and black. There are 4 sizes - 3/16, 1/2, 3/4 and 1.

Once the salesperson enters the color and size in different cells, I need to be able to look up the price that corresponds to these two variables and display the selected price in a cell. I'm not having much luck with vlookup.

Any help would be much appreciated.

**Ruthki** · 10-25-2006, 11:59 AM

Try

Set up a price list with columns being Colour (col A), thickness (col B), price (col c) and then every possible combination below

=SUMPRODUCT(--(A2:A13=F2),--(B2:B13=F3),(C2:C13))

Assumes salesman variables are input into cells f2 and f3

R

**Stan T** · 10-25-2006, 02:58 PM

Ruthki,

Thanks for helping me out here. I get "natural" instead of my correct number. Here is what I did:

Col A Col B Col C
natural 3/16 27.8
natural 1/2 42.23
natural 3/4 49.23
natural 1 59.18
white 3/16 30.98
white 1/2 48.88
white 3/4 56.51
white 1 67.61
black 3/16 31.91
black 1/2 50.38
black 3/4 58.25
black 1 69.64

G6 is the size input (3/16 in this example), H6 is the color input (natural in this example). Here is my formula:

=SUMPRODUCT(--(A27:A38=G6),--(B27:B38=H6),(C27:C38))

I should get 27.8 but I get "natural" instead. What am I doing wrong? Thanks