Easiest way to have a dropdown based on 2 conditions

[FJPunisher]

Hello Experts,

I am looking for help on below, I am sure this is possible but can’t find a most efficient way to do this.

• I will capture ID as text in a cell, this can be A_100, A_200, A_300, A_400, A_500 or A_600, (DONE)
• Based on that ID, I am using VB to paste the days that are associated with that ID, (DONE)
• Now based on the ID & DAY i want to show a dropdown list with up to three options. (HELP!)

In short, what is the easiest way to have dropdown list based on 2 conditions…
e.g.
IF ID = A_100 & Day = D_SUN Then Dropdown = 5.50, 6.6, 4.5

 

[kweaver]

With a helper column, I tried this:

ReplicateProductsBYCusomers.xlsx
	G	H	I	J	K	L	M	N	O
1							A_100	D_Thur
2	A_100	D_Sun	5.50	6.6	4.5				10
3	A_100	D_Mon	2.00	2.2	3.5				8.1
4	A_100	D_Tue							6.2
5	A_100	D_Wed
6	A_100	D_Thur	10.00	8.1	6.2
7	A_100	D_Fri
8	A_200	D_Sun
9	A_200	D_Mon
10	A_200	D_Tue
11	A_200	D_Wed
12	A_200	D_Thur
13	A_200	D_Fri
Sheet4

Cell Formulas
Range	Formula
O2:O4	O2	=TRANSPOSE((INDEX($I$2:$K$13,MATCH($M$1&$N$1,$G$2:$G$13&$H$2:$H$13,0),{1,2,3})))
Dynamic array formulas.

 

[FJPunisher]

Hi Kweaver,

Thank you for this, truly appreciate it!



However, it didn’t work for me, not sure what I am doing wrong (Screenshot one).

In an effort to simplify the data to meet other requirements, I have made some changes to the table, would appreciate if you can update your solution and also point out anything I am doing wrong as below didn’t work for me:



O2
=TRANSPOSE((INDEX($I$2:$K$13,MATCH($M$1&$N$1,$G$2:$G$13&$H$2:$H$13,0),{1,2,3})))
=INDIRECT(TRANSPOSE((INDEX($G$3:$K$29,MATCH($M$1&$N$1,$G$3:$G$29&$I$3:$I$29,0),{1,2,3}))),FALSE)



Data Validation
=O2#
=INDIRECT(TRANSPOSE((INDEX($G$3:$K$29,MATCH($M$1&$N$1,$G$3:$G$29&$I$3:$I$29,0),{1,2,3}))),FALSE)
=TRANSPOSE((INDEX($I$2:$K$13,MATCH($M$1&$N$1,$G$2:$G$13&$H$2:$H$13,0),{1,2,3})))

Book1.xlsx
	F	G	H	I	J	K	L	M	N	O	P	Q	R
1							I will use VLOOKUP for this				A_100	D_Wed
2		ID	PRO.ID	DAY	Dropdown		Other Data A	Other Data B	Other Data C				#VALUE!
3		A_100		D_Sun	Option 1	5.50
4		A_100		D_Sun	Option 2	6.6
5		A_100		D_Sun	Option 3	4.5
6		A_100		D_Mon	Option 1	2.00
7		A_100		D_Mon	Option 2	2.2
8		A_100		D_Mon	Option 3	3.5
9		A_100		D_Tue	Option 1	3.50
10		A_100		D_Tue	Option 2	0
11		A_100		D_Tue	Option 3	0
12		A_100		D_Wed	Option 1	0.10
13		A_100		D_Wed	Option 2	2.4
14		A_100		D_Wed	Option 3	1.2
15		A_100		D_Thur	Option 1	0.00
16		A_100		D_Thur	Option 2	4.5
17		A_100		D_Thur	Option 3	5.50
18		A_100		D_Fri	Option 1	2.00
19		A_100		D_Fri	Option 2	3.5
20		A_100		D_Fri	Option 3	2.00
21		A_200		D_Sun	Option 1	1.50
22		A_200		D_Sun	Option 2	1.2
23		A_200		D_Sun	Option 3	2.1
24		A_200		D_Mon	Option 1	1.80
25		A_200		D_Mon	Option 2	0
26		A_200		D_Mon	Option 3	2.1
27		A_200		D_Thur	Option 1	0.00
28		A_200		D_Thur	Option 2	2.1
29		A_200		D_Thur	Option 3	1.80
Sheet1 (2)

Cell Formulas
Range	Formula
R2	R2	=TRANSPOSE((INDEX($G$3:$K$29,MATCH($P$1&$Q$1,$G$3:$G$29&$I$3:$I$29,0),{1,2,3})))

Named Ranges
Name	Refers To	Cells
'Sheet1 (2)'!A_100D_Sun	='Sheet1 (2)'!$K$10:$M$10	R2

Cells with Data Validation
Cell	Allow	Criteria
O8	List	=INDIRECT(#REF!)

 

[FJPunisher]

With a helper column, I tried this:

ReplicateProductsBYCusomers.xlsx
	G	H	I	J	K	L	M	N	O
1							A_100	D_Thur
2	A_100	D_Sun	5.50	6.6	4.5				10
3	A_100	D_Mon	2.00	2.2	3.5				8.1
4	A_100	D_Tue							6.2
5	A_100	D_Wed
6	A_100	D_Thur	10.00	8.1	6.2
7	A_100	D_Fri
8	A_200	D_Sun
9	A_200	D_Mon
10	A_200	D_Tue
11	A_200	D_Wed
12	A_200	D_Thur
13	A_200	D_Fri
Sheet4

Cell Formulas
Range	Formula
O2:O4	O2	=TRANSPOSE((INDEX($I$2:$K$13,MATCH($M$1&$N$1,$G$2:$G$13&$H$2:$H$13,0),{1,2,3})))
Dynamic array formulas.

View attachment 35035

View attachment 35036

Thanks Kweaver!

 

[kweaver]

I'm afraid I don't understand what you now want to do as you've totally changed the layout from the original post.

 

[FJPunisher]

Hi - I want to pick up the options from Columns now instead of Row.

 

[kweaver]

If you make R3:

=FILTER(K3:K38,($P$1&$Q$1=G3:G38&I3:I38))

and reference a drop down in, say, P2 using =R3# does that work?
(change the 38 as needed)

 

[FJPunisher]

I get an error saying Function is not valid.
Do you know what i am doing wrong in the original solution you suggested?

What does Range 02:04 mean in your original post

Range	Formula
Cell Formulas
O2:O4	O2	=TRANSPOSE((INDEX($I$2:$K$13,MATCH($M$1&$N$1,$G$2:$G$13&$H$2:$H$13,0),{1,2,3})))
Dynamic array formulas.

Mine is :

Range	Formula
Cell Formulas
R2	R2	=TRANSPOSE((INDEX($G$3:$K$29,MAT

Also the below source (=02#) comes up as invalid, =INDIRECT(O2) is also not working for me in dropdown sournce.

 

[kweaver]

Ah, Excel 2019 probably doesn't have FILTER. Sorry about that.

What does Range 02:04 mean in your original post? That's an "O" and not a "0". Cells O2:O4

 

[FJPunisher]

Just figured out, i wasnt using crt shift enter, your first solution is working now, but gives me one one option...