Possible Vlookup?

cb123

New Member
Joined
Jul 1, 2016
Messages
34
Hi there, hoping someone can help me. If you can many thanks in advance. I've been working on a project and this hurdle is my last missing piece.

Example:

I have a vlookup in place which returns a price based on 2 selections. firstly in A1 i have a data validation list of clothing items and then in B2 i have a data validation list of colours. In C1 I have my vlookup which works and returns my price based on me choosing, for example, a shirt in blue.

What i need though is that once that single choice of shirt in blue is made i would like a separate area which automatically populates with prices for shirts only in the other 5 colours available.

Many thanks!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Try this:
Book1
ABCDEFGHIJKLMNOP
1Shirt1ShirtsColorPriceColor Based A1PriceShirt Based B2PricePrice Based Both filling
2OrangeShirt1Blue120Blue120Shirt3149157
3Shirt2Red139Yellow182Shirt5192
4Shirt3Orange149Brown160Shirt1157
5Shirt4Black155Orange157  
6Shirt5Brown153    
7Shirt6White150    
8Shirt1Yellow182    
9Shirt2Purple142
10Shirt3Blue135
11Shirt4Red146
12Shirt5Orange192
13Shirt6Black116
14Shirt1Brown160
15Shirt2White198
16Shirt3Yellow131
17Shirt4Purple136
18Shirt5Blue159
19Shirt6Red192
20Shirt1Orange157
21Shirt2Black137
22Shirt3Brown169
23
Sheet2
Cell Formulas
RangeFormula
I2,I8I2=IFERROR(INDEX($F$2:$F$22,SMALL(IF($E$2:$E$22=$A$1,ROW($E$2:$E$22)-ROW(INDEX($E$2:$E$22,1,1))+1),ROWS($I$1:I1))),"")
J2:J8J2=IFNA(INDEX($G$2:$G$22,MATCH(1,($E$2:$E$22=$A$1)*($F$2:$F$22=I2),0)),"")
I3:I7I3=IFERROR(INDEX($F$2:$F$22,SMALL(IF($E$2:$E$22=$A$1,ROW($E$2:$E$22)-ROW(INDEX($E$2:$E$22,1,1))+1),ROWS($I$1:I2))),"")
L2:L8L2=IFERROR(INDEX($E$2:$E$22,SMALL(IF($F$2:$F$22=$B$2,ROW($F$2:$F$22)-ROW(INDEX($F$2:$F$22,1,1))+1),ROWS($L$1:L1))),"")
M2:M8M2=IFNA(INDEX($G$2:$G$22,MATCH(1,($F$2:$F$22=$B$2)*($E$2:$E$22=L2),0)),"")
O2O2=IFNA(IF(B2="",INDEX($G$2:$G$22,MATCH(1,($E$2:$E$22=$A$1)*($F$2:$F$22=I2),0)),IF(A1="",INDEX($G$2:$G$22,MATCH(1,($F$2:$F$22=$B$2)*($E$2:$E$22=L2),0)),INDEX($G$2:$G$22,MATCH(1,($F$2:$F$22=$B$2)*($E$2:$E$22=$A$1),0)))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Thank you that is looking really good!. Just one question please if you could help further? I would list as you have in column E but to list it like shirt 1, shirt 1, shirt 1, shirt 1 and then in column F to have Blue, yellow, brown, orange.

Is there a way to only have shirt 1 listed once in the drop down list in A1? This would keep everything really clean for me.
 
Upvote 0
you should extract unique value from table first then add that list to Data Validation List for Dropdown.
This is formula for extracting unique Value (Press CTRL+SHIFT+ENTER) and drag it down:
Excel Formula:
=IFNA(INDEX($E$2:$E$22,MATCH(0,COUNTIF($I$11:I11,$E$2:$E$22),0)),"")
See Cell I12
IF Questionairre2.xlsx
ABCDEFGHIJKLMNOP
1shirt1ShirtsColorPriceColor Based A1PriceShirt Based B2PricePrice Based Both filling
2OrangeShirt1Blue120Blue120Shirt3149157
3Shirt2Red139Yellow182Shirt5192
4Shirt3Orange149Brown160Shirt1157
5Shirt4Black155Orange157  
6Shirt5Brown153    
7Shirt6White150    
8Shirt1Yellow182    
9Shirt2Purple142
10Shirt3Blue135
11Shirt4Red146Unique Values
12Shirt5Orange192Shirt1
13Shirt6Black116Shirt2
14Shirt1Brown160Shirt3
15Shirt2White198Shirt4
16Shirt3Yellow131Shirt5
17Shirt4Purple136Shirt6
18Shirt5Blue159 
19Shirt6Red192 
20Shirt1Orange157
21Shirt2Black137
22Shirt3Brown169
23
Sheet1
Cell Formulas
RangeFormula
I2:I8I2=IFERROR(INDEX($F$2:$F$22,SMALL(IF($E$2:$E$22=$A$1,ROW($E$2:$E$22)-ROW(INDEX($E$2:$E$22,1,1))+1),ROWS($I$1:I1))),"")
J2:J8J2=IFNA(INDEX($G$2:$G$22,MATCH(1,($E$2:$E$22=$A$1)*($F$2:$F$22=I2),0)),"")
L2:L8L2=IFERROR(INDEX($E$2:$E$22,SMALL(IF($F$2:$F$22=$B$2,ROW($F$2:$F$22)-ROW(INDEX($F$2:$F$22,1,1))+1),ROWS($L$1:L1))),"")
M2:M8M2=IFNA(INDEX($G$2:$G$22,MATCH(1,($F$2:$F$22=$B$2)*($E$2:$E$22=L2),0)),"")
O2O2=IFNA(IF(B2="",INDEX($G$2:$G$22,MATCH(1,($E$2:$E$22=$A$1)*($F$2:$F$22=I2),0)),IF(A1="",INDEX($G$2:$G$22,MATCH(1,($F$2:$F$22=$B$2)*($E$2:$E$22=L2),0)),INDEX($G$2:$G$22,MATCH(1,($F$2:$F$22=$B$2)*($E$2:$E$22=$A$1),0)))),"")
I12:I19I12=IFNA(INDEX($E$2:$E$22,MATCH(0,COUNTIF($I$11:I11,$E$2:$E$22),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
Criteria=Sheet1!$A$1O2, I2:J8
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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
Back
Top