lookup price by style and color.

[mspincus]

I have a validation cell with all my styles and another cell validated with color choices. I want to the third cell to show the price. style 1 with color 2 is $500 but style 1 with color 3 is $600. I have 300 products each with 8 colors

please help
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[lenze]

Hi and welcome to the Board!!
I do not envy you with this one. You will have some heavy upfront work. WHen you say 300 products, it that the same as Style??

lenze

 

[mspincus]

I have a furniture store. Titan is the style with ottomon sofa theater roughly 9 modifications of titan I. Then have 10 titans. I the have titan ottomon with 8 colors

 

[lenze]

I think I know what you mean, but I'm not sure!!
What are some examples of Style Names?
What are some examples of your colors?
You can make a 2 dimensional table and use INDEX/MATCH. A small example would look like this

  A               B    C     D    E  F      G               
1                 Blue Green Red                            
2 Titan Ottoman 1 500  500   600     Style: Titan Ottoman 2 
3 Titan Ottoman 2 300  350   400     Color: Green           
4 Titan Ottoman 3 600  600   500                            
5 Titan Ottoman 4 300  300   400            350             
Sheet1
[Table-It] version 09 by Erik Van Geit

RANGE FORMULA 
G5    =INDEX($B$2:$D$5,MATCH($G$2,$A$2:$A$5,0),MATCH($G$3,$B$1:$D$1,))
[Table-It] version 09 by Erik Van Geit

The formula in G5 looks at G2 to get the style and G3 to get the Color and returns the price at the intersection of the two!
The upfornt work is creating a table for with ALL 300+ products and Colors. Once that's done, then getting the price is easy!!
HTH
lenze

 

[gmacisaac]

One approach could be;

If you have several styles and many colors your third cell could be a formula using a concatenate function inside a vlookup function.

You will first need a listing (source table) of all different combinations with thier appropriate prices. (i.e. Titan1-Red is $500) In your third cell have a formula as such;
=vlookup(concatenate(titan1,"-",red),source table range,2,false)

Source Table
Titan1-red 500
titan1-blue 525
titan1-grey 515

Glenn