extracting a list with 2 criteria

[raccoon588]

i am trying to add another criteria to this formula so that once i copy the formula down it will generate a list. it works with one criteria but i need it to use two.
=IFERROR(INDEX($A:$A,SMALL(IF($D:$D=O$3,ROW($D:$D)-ROW($D$1)+1),ROWS($D$1:$D1))),"")

how would i go about adding the second criteria to the above formula?

 

[dave2018]

if you still havent found the solution, can you post like 10 rows of sample data, and pls specify what your criteria should be.

 

[Aladin Akyurek]

i am trying to add another criteria to this formula so that once i copy the formula down it will generate a list. it works with one criteria but i need it to use two.
=IFERROR(INDEX($A:$A,SMALL(IF($D:$D=O$3,ROW($D:$D)-ROW($D$1)+1),ROWS($D$1:$D1))),"")

how would i go about adding the second criteria to the above formula?

Try to avoid referencing whole columns for reasons of efficiency.

Assuming that the data start at row 2 and the second criteria is in O2 which column C is supposed to be equal to...

In O1 enter:

=COUNTIFS(C2:C4000,O2,D2:D4000,O3)

In O5 control+shift+enter, not just enter, and copy down:

=IF(ROWS(O$5:O5)>O$1,"",INDEX(A$2:A$4000,SMALL(IF($C$2:$C$4000=O$2,IF($D$2:$D$4000=O$3,ROW(A$2:A$400)-ROW(A$2)+1)),ROWS(O$5:O5))))

This should create a sublist based on two criteria in O5 downwards.

 

[raccoon588]

Id like to pull all the name (just the names) that are both in Dept 205 and Position Lamp maker using a formula. here is some data.

[TABLE="width: 647"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Dept[/TD]
[TD]Shift[/TD]
[TD]Area[/TD]
[TD]Position[/TD]
[TD]Badge Number[/TD]
[TD]Hire Date[/TD]
[/TR]
[TR]
[TD]Bezio, Wanda[/TD]
[TD]372[/TD]
[TD]1[/TD]
[TD]SSL[/TD]
[TD]Lamp Maker[/TD]
[TD]10718[/TD]
[TD]November 26, 1984[/TD]
[/TR]
[TR]
[TD]Boston, Nick[/TD]
[TD]205[/TD]
[TD]1[/TD]
[TD]SSL[/TD]
[TD]Lamp Maker[/TD]
[TD]15830[/TD]
[TD]August 8, 2016[/TD]
[/TR]
[TR]
[TD]Burgess, Ross[/TD]
[TD]351[/TD]
[TD]1[/TD]
[TD]SSL[/TD]
[TD]Lamp Maker[/TD]
[TD]77055[/TD]
[TD]January 15, 2018[/TD]
[/TR]
[TR]
[TD]Chase, Brittany[/TD]
[TD]205[/TD]
[TD]1[/TD]
[TD]SSL[/TD]
[TD]Lamp Maker[/TD]
[TD]15801[/TD]
[TD]January 18, 2016[/TD]
[/TR]
[TR]
[TD]Collelo, Alex[/TD]
[TD]372[/TD]
[TD]1[/TD]
[TD]SSL[/TD]
[TD]Lamp Maker[/TD]
[TD]77059[/TD]
[TD]January 29, 2018[/TD]
[/TR]
[TR]
[TD]Ervin, Lorelei[/TD]
[TD]372[/TD]
[TD]1[/TD]
[TD]SSL[/TD]
[TD]Lamp Maker[/TD]
[TD]15781[/TD]
[TD]August 3, 2015[/TD]
[/TR]
[TR]
[TD]Furlong, Ross[/TD]
[TD]301[/TD]
[TD]1[/TD]
[TD]SSL[/TD]
[TD]Mechanic[/TD]
[TD]15477[/TD]
[TD]May 22, 2000[/TD]
[/TR]
[TR]
[TD]Gerini, Josh[/TD]
[TD]351[/TD]
[TD]1[/TD]
[TD]SSL[/TD]
[TD]Lamp Maker[/TD]
[TD]15746[/TD]
[TD]May 19, 2014[/TD]
[/TR]
[TR]
[TD]Goudreault, Tammy[/TD]
[TD]372[/TD]
[TD]1[/TD]
[TD]SSL[/TD]
[TD]Lamp Maker[/TD]
[TD]15761[/TD]
[TD]January 19, 2015[/TD]
[/TR]
[TR]
[TD]Kelley, Lia[/TD]
[TD]372[/TD]
[TD]1[/TD]
[TD]SSL[/TD]
[TD]Lamp Maker[/TD]
[TD]15795[/TD]
[TD]November 23, 2015[/TD]
[/TR]
[TR]
[TD]Lawson, Heather[/TD]
[TD]351[/TD]
[TD]1[/TD]
[TD]SSL[/TD]
[TD]Lamp Maker[/TD]
[TD]15778[/TD]
[TD]July 27, 2015[/TD]
[/TR]
[TR]
[TD]Mackey, LeeAnn[/TD]
[TD]301[/TD]
[TD]1[/TD]
[TD]SSL[/TD]
[TD]Lamp Maker[/TD]
[TD]15548[/TD]
[TD]January 2, 2001[/TD]
[/TR]
[TR]
[TD]McLemore, Sandy[/TD]
[TD]201[/TD]
[TD]1[/TD]
[TD]SSL[/TD]
[TD]Lamp Maker[/TD]
[TD]15029[/TD]
[TD]May 17, 1993[/TD]
[/TR]
[TR]
[TD]Mitchell, Robyn[/TD]
[TD]102[/TD]
[TD]1[/TD]
[TD]SSL[/TD]
[TD]Material Handler[/TD]
[TD]15357[/TD]
[TD]August 30, 1999[/TD]
[/TR]
[TR]
[TD]Moy, Jennifer[/TD]
[TD]301[/TD]
[TD]1[/TD]
[TD]SSL[/TD]
[TD]Lamp Maker[/TD]
[TD]15789[/TD]
[TD]November 2, 2015[/TD]
[/TR]
[TR]
[TD]Ouellette, Courtney[/TD]
[TD]205[/TD]
[TD]1[/TD]
[TD]SSL[/TD]
[TD]Lamp Maker[/TD]
[TD]77015[/TD]
[TD]July 10, 2017[/TD]
[/TR]
[TR]
[TD]Pelchat, Linda[/TD]
[TD]102[/TD]
[TD]1[/TD]
[TD]SSL[/TD]
[TD]Lamp Maker\GL[/TD]
[TD]15794[/TD]
[TD]November 23, 2015[/TD]
[/TR]
[TR]
[TD]Pelchat, Randy[/TD]
[TD]205[/TD]
[TD]1[/TD]
[TD]SSL[/TD]
[TD]Mechanic[/TD]
[TD]15507[/TD]
[TD]September 5, 2000[/TD]
[/TR]
[TR]
[TD]Richards, Tina[/TD]
[TD]372[/TD]
[TD]1[/TD]
[TD]SSL[/TD]
[TD]Lamp Maker[/TD]
[TD]15737[/TD]
[TD]March 3, 2014[/TD]
[/TR]
[TR]
[TD]Rockwell, Tyler[/TD]
[TD]205[/TD]
[TD]1[/TD]
[TD]SSL[/TD]
[TD]Lamp Maker[/TD]
[TD]77050[/TD]
[TD]December 11, 2017[/TD]
[/TR]
[TR]
[TD]Roth, Jen[/TD]
[TD]301[/TD]
[TD]1[/TD]
[TD]SSL[/TD]
[TD]Lamp Maker[/TD]
[TD]11024[/TD]
[TD]February 3, 1986[/TD]
[/TR]
[TR]
[TD]Traxler, Kimberly[/TD]
[TD]372[/TD]
[TD]1[/TD]
[TD]SSL[/TD]
[TD]Lamp Maker\GL[/TD]
[TD]76957[/TD]
[TD]February 20, 2017[/TD]
[/TR]
</tbody>[/TABLE]

 

[dave2018]

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H	I
1	Name	Dept	Shift	Area	Position	Badge Number	Hire Date
2	Bezio, Wanda	372	1	SSL	Lamp Maker	10718	26-Nov-84		Boston, Nick
3	Boston, Nick	205	1	SSL	Lamp Maker	15830	8-Aug-16		Chase, Brittany
4	Burgess, Ross	351	1	SSL	Lamp Maker	77055	15-Jan-18		Ouellette, Courtney
5	Chase, Brittany	205	1	SSL	Lamp Maker	15801	18-Jan-16		Rockwell, Tyler
6	Collelo, Alex	372	1	SSL	Lamp Maker	77059	29-Jan-18
7	Ervin, Lorelei	372	1	SSL	Lamp Maker	15781	3-Aug-15
8	Furlong, Ross	301	1	SSL	Mechanic	15477	22-May-00
9	Gerini, Josh	351	1	SSL	Lamp Maker	15746	19-May-14
10	Goudreault, Tammy	372	1	SSL	Lamp Maker	15761	19-Jan-15
11	Kelley, Lia	372	1	SSL	Lamp Maker	15795	23-Nov-15
12	Lawson, Heather	351	1	SSL	Lamp Maker	15778	27-Jul-15
13	Mackey, LeeAnn	301	1	SSL	Lamp Maker	15548	2-Jan-01
14	McLemore, Sandy	201	1	SSL	Lamp Maker	15029	17-May-93
15	Mitchell, Robyn	102	1	SSL	Material Handler	15357	30-Aug-99
16	Moy, Jennifer	301	1	SSL	Lamp Maker	15789	2-Nov-15
17	Ouellette, Courtney	205	1	SSL	Lamp Maker	77015	10-Jul-17
18	Pelchat, Linda	102	1	SSL	Lamp Maker\GL	15794	23-Nov-15
19	Pelchat, Randy	205	1	SSL	Mechanic	15507	5-Sep-00
20	Richards, Tina	372	1	SSL	Lamp Maker	15737	3-Mar-14
21	Rockwell, Tyler	205	1	SSL	Lamp Maker	77050	11-Dec-17
22	Roth, Jen	301	1	SSL	Lamp Maker	11024	3-Feb-86
23	Traxler, Kimberly	372	1	SSL	Lamp Maker\GL	76957	20-Feb-17
Sheet4

Cell Formulas
Range	Formula
I2	{=IFERROR(INDEX($A$2:$A$23,SMALL(IF(($B$2:$B$23=205)*($E$2:$E$23="Lamp Maker"),ROW($A$2:$A$23)-ROW($A$2)+1),ROWS($I$2:I2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[dave2018]

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H	I
1	Name	Dept	Shift	Area	Position	Badge Number	Hire Date
2	Bezio, Wanda	372	1	SSL	Lamp Maker	10718	26-Nov-84		Boston, Nick
3	Boston, Nick	205	1	SSL	Lamp Maker	15830	8-Aug-16		Chase, Brittany
4	Burgess, Ross	351	1	SSL	Lamp Maker	77055	15-Jan-18		Ouellette, Courtney
5	Chase, Brittany	205	1	SSL	Lamp Maker	15801	18-Jan-16		Rockwell, Tyler
6	Collelo, Alex	372	1	SSL	Lamp Maker	77059	29-Jan-18
7	Ervin, Lorelei	372	1	SSL	Lamp Maker	15781	3-Aug-15
8	Furlong, Ross	301	1	SSL	Mechanic	15477	22-May-00
9	Gerini, Josh	351	1	SSL	Lamp Maker	15746	19-May-14
10	Goudreault, Tammy	372	1	SSL	Lamp Maker	15761	19-Jan-15
11	Kelley, Lia	372	1	SSL	Lamp Maker	15795	23-Nov-15
12	Lawson, Heather	351	1	SSL	Lamp Maker	15778	27-Jul-15
13	Mackey, LeeAnn	301	1	SSL	Lamp Maker	15548	2-Jan-01
14	McLemore, Sandy	201	1	SSL	Lamp Maker	15029	17-May-93
15	Mitchell, Robyn	102	1	SSL	Material Handler	15357	30-Aug-99
16	Moy, Jennifer	301	1	SSL	Lamp Maker	15789	2-Nov-15
17	Ouellette, Courtney	205	1	SSL	Lamp Maker	77015	10-Jul-17
18	Pelchat, Linda	102	1	SSL	Lamp Maker\GL	15794	23-Nov-15
19	Pelchat, Randy	205	1	SSL	Mechanic	15507	5-Sep-00
20	Richards, Tina	372	1	SSL	Lamp Maker	15737	3-Mar-14
21	Rockwell, Tyler	205	1	SSL	Lamp Maker	77050	11-Dec-17
22	Roth, Jen	301	1	SSL	Lamp Maker	11024	3-Feb-86
23	Traxler, Kimberly	372	1	SSL	Lamp Maker\GL	76957	20-Feb-17
Sheet4

Cell Formulas
Range	Formula
I2	{=IF(ROWS($I$2:I2)<=SUM(IF(($B$2:$B$23=205)*($E$2:$E$23="Lamp Maker"),1)),INDEX($A$2:$A$23,SMALL(IF(($B$2:$B$23=205)*($E$2:$E$23="Lamp Maker"),ROW($A$2:$A$23)-ROW($A$2)+1),ROWS($I$2:I2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

I think this one is faster if u have a lot of data

 

[dave2018]

Just adjust any formula's refrences to the size of your data, and drag down your formula as much as u want...

 

[Aladin Akyurek]

Id like to pull all the name (just the names) that are both in Dept 205 and Position Lamp maker using a formula. here is some data.

[...]

Just substitute the conditions and adjust the condition ranges in the formulas you are already given...

O1 >> just enter

=COUNTIFS(B2:B4000,O2,E2:E4000,O3)

O2 : 205 [the department condition for column B]

O3 : Lamp Maker [the Position condition for column E]

O5 >> control+shift+enter, not just enter, and copy down:

=IF(ROWS(O$5:O5)>O$1,"",INDEX(A$2:A$4000,SMALL(IF($B$2:$B$4000=O$2,IF($E$2:$E$4000=O$3,ROW(A$2:A$400)-ROW(A$2)+1)),ROWS(O$5:O5))))

 

[dave2018]

Just substitute the conditions and adjust the condition ranges in the formulas you are already given...

O1 >> just enter

=COUNTIFS(B2:B4000,O2,E2:E4000,O3)

O2 : 205 [the department condition for column B]

O3 : Lamp Maker [the Position condition for column E]

O5 >> control+shift+enter, not just enter, and copy down:

=IF(ROWS(O$5:O5)>O$1,"",INDEX(A$2:A$4000,SMALL(IF($B$2:$B$4000=O$2,IF($E$2:$E$4000=O$3,ROW(A$2:A$400)-ROW(A$2)+1)),ROWS(O$5:O5))))

Yes, that way you can change the criteria.