Show multiple results from data sheet, based off 2 criteria selected in drop down menus

[OzAlly]

so I've got a spreadsheet set up for Trivia questions and responses. I am creating an easy database type of thing to look up and use.

The idea is that Based on a "Category" (Arts & culture, Brands & logos, Geography etc...). This is already a drop down list
Then there will be a "Criteria" (i.e. Geography has a list of all countries, European countries, African countries etc... )within that category. This is also a drop down list (dependant on the first list)
I also have a drop down list of the alphabet.

What i want to be able to do is to be able to select responses off the 'Criteria' list by letter of the Alphabet.
(e.g. Be able to select countries beginning with A) so then I get a result which has multiple answers i.e. Australia, Andorra, Austria etc...

I was able to get it functioning when i was looking at a single list - but when i have a full database looking at multiple lists of multiple responses I can't seem to get it to work. I can put the data on separate tabs - but i still have the same issue

 

[Toadstool]

Hi OzAlly,

I'm not sure if I'm following your requirement completely, and your later version of Excel have have a better approach, but here's my Excel 2016 approach by building the LoV.

OzAlley.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
1								Built LoV			Category	Criteria	Response
2								Australia			Geography	Countries	Australia
3								Austria			Geography	Countries	Austria
4	Category	Criteria (Starting with letter…)	Letter	Response				Angola			Geography	Countries	France
5	Geography	Countries	A								Geography	Countries	Angola
6											Food	Countries	France
7											Food	Countries	Australia
8											Arts	Person	Ned Kelly
9											Arts	Date	1932
10
11
Sheet1

Cell Formulas
Range	Formula
I2:I11	I2	=IFERROR(INDEX($N$2:$N$9999,AGGREGATE(15,6,ROW($L$2:$L$9999)-ROW($L$1)/(($L$2:$L$9999=$B$5)*($M$2:$M$9999=$C$5)*(LEFT($N$2:$N$9999,1)=$D$5)),ROW()-ROW($I$1))),"")

Cells with Data Validation
Cell	Allow	Criteria
E5	List	=OFFSET($I$2,,,COUNTIF($I$2:$I$99,"> "))

 

[OzAlly]

Hi OzAlly,

I'm not sure if I'm following your requirement completely, and your later version of Excel have have a better approach, but here's my Excel 2016 approach by building the LoV.

OzAlley.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
1								Built LoV			Category	Criteria	Response
2								Australia			Geography	Countries	Australia
3								Austria			Geography	Countries	Austria
4	Category	Criteria (Starting with letter…)	Letter	Response				Angola			Geography	Countries	France
5	Geography	Countries	A								Geography	Countries	Angola
6											Food	Countries	France
7											Food	Countries	Australia
8											Arts	Person	Ned Kelly
9											Arts	Date	1932
10
11
Sheet1

Cell Formulas
Range	Formula
I2:I11	I2	=IFERROR(INDEX($N$2:$N$9999,AGGREGATE(15,6,ROW($L$2:$L$9999)-ROW($L$1)/(($L$2:$L$9999=$B$5)*($M$2:$M$9999=$C$5)*(LEFT($N$2:$N$9999,1)=$D$5)),ROW()-ROW($I$1))),"")

Cells with Data Validation
Cell	Allow	Criteria
E5	List	=OFFSET($I$2,,,COUNTIF($I$2:$I$99,"> "))

Thank you so much for your help!
Not quite as elegant a solution as I was hoping - and i haven't figured out the best way to get ti to work with the data on another sheet - however it is working as I was hoping - so thank you very much!
I'll have a play around to see if i can shift the data to another tab (so the front page only have the question criteria)
Thank you!

 

[Toadstool]

You're welcome!
...and maybe somebody with your later version of Excel will provide a simpler solution (FILTER and UNIQUE?) but until then here it is split over 2 sheets.

OzAlley.xlsx
	A	B	C	D	E	F
1	Built LoV			Category	Criteria	Response
2	Australia			Geography	Countries	Australia
3	Austria			Geography	Countries	Austria
4	Angola			Geography	Countries	France
5				Geography	Countries	Angola
6				Food	Countries	France
7				Food	Countries	Australia
8				Arts	Person	Ned Kelly
9				Arts	Date	1932
10
11
12
Sheet2

Cell Formulas
Range	Formula
A2:A12	A2	=IFERROR(INDEX($F$2:$F$13,AGGREGATE(15,6,ROW($D$2:$D$13)-ROW($D$1)/(($D$2:$D$13=Sheet1!$B$5)*($E$2:$E$13=Sheet1!$C$5)*(LEFT($F$2:$F$13,1)=Sheet1!$D$5)),ROW()-ROW($A$1))),"")

OzAlley.xlsx
	A	B	C	D	E	F
1
2
3
4		Category	Criteria (Starting with letter…)	Letter	Response
5		Geography	Countries	A
6
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
E5	List	=OFFSET(Sheet2!$A$2,,,COUNTIF(Sheet2!$A$2:$A$99,"> "))