lookup price by style and color.

mspincus

New Member
Joined
Jul 30, 2010
Messages
30
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">
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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

New Member
Joined
Jul 30, 2010
Messages
30
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

Legend
Joined
Feb 18, 2002
Messages
13,690
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
Code:
  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
Code:
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

New Member
Joined
Jul 29, 2010
Messages
1
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,087
Messages
5,599,654
Members
414,325
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top