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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

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

Replies
0
Views
944
Replies
0
Views
662
Replies
0
Views
164
Replies
0
Views
343
Replies
15
Views
371

1,171,631
Messages
5,876,556
Members
433,200
Latest member
CoolBlue_

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.

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