Create list based on user selection from multiple drop down boxes

[ten1jmj]

I am trying to create a list on one worksheet based on data from another worksheet using two drop down boxes. I have a main spreadsheet that houses a list of operators, with their corresponding status (active, dropped, termed) and supervisor name. I would like to be able to select the supervisor name and status and the result be a list of operators that match that information. I currently have the formula =IF(ROWS($C$17:C17)<=$B$16,INDEX(Data!$A$2:$A$1048576,AGGREGATE(15,3,(Data!$H$2:$H$1048576=$A$1)/(Data!$H$2:$H$1048576=$A$1)*(ROW(Data!$H$2:$H$1048576)-ROW(Data!$H$1)),ROWS($C$17:C17))),"") which allows me to select a drop down to get a list based on the supervisor name, but I can't figure out how to add the status to the formula.

$B$16 is a reference to a helper cell to do a count: =COUNTIF(Data!$H$2:$H$1048576,$A$1)

Sheet1 = DATA
A B C D E F G H
1Name Carrier Position Status Model S.Date T.Date Supv
2 AA Active A Smith
3 BB Active J Doe
4 CC Term A Smith
5 DD Active J Doe
6 EE Term J Doe
7 FF Dropped A Smith
8 GG Active A Smith

Sheet2
A
1 Dropdown box to select Supv
2 Dropdown box to select Status

Example:

Supv: A Smith
Status: Active
Resulting list:
AA
GG

 

[RasGhul]

Hi Ten,

It would be faster for calculation if you convert your data to a table first rather than indexing the whole sheet.

I've built the countif into formula if you dont need B16 Helper cell.

Book1
	A	B	C	D	E	F	G	H
1	1Name	Carrier	Position	Status	Model	S.Date	T.Date	Supv
2	AA	Active	A	Smith
3	BB	Active	J	Doe
4	CC	Term	A	Smith
5	DD	Active	J	Doe
6	EE	Term	J	Doe
7	FF	Dropped	A	Smith
8	GG	Active	A	Smith
data

Book1
	A	B	C	D	E	F	G	H
1	Smith
2	Active
3
4
5	1Name	Carrier	Position	Status	Model	S.Date	T.Date	Supv
6	AA	Active	A	Smith
7	GG	Active	A	Smith
Sheet2

Cell Formulas
Range	Formula
A6	{=IF(ROWS(Sheet2!$A$6:A6)>COUNTIFS(data!$D$2:$D$8,Sheet2!$A$1,data!$B$2:$B$8,Sheet2!$A$2),"",INDEX(data!A$2:A$8,SMALL(IF(data!$D$2:$D$8&data!$B$2:$B$8=Sheet2!$A$1&Sheet2!$A$2,ROW(data!$D$2:$D$8)-ROW(data!$D$2)+1),ROWS(Sheet2!$A$6:A6))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ten1jmj]

RasGhul,

thank you for much for your assistance. It worked perfectly.