# lookup price by style and color.

#### mspincus

##### New Member
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

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

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### lenze

##### Legend
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
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
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
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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,991
Messages
5,856,683
Members
431,828
Latest member
kARTIK12345

### 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.

### Which adblocker are you using?

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

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