extracting a list with 2 criteria

R

raccoon588

Board Regular
Joined
Aug 5, 2016
Messages
118
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?
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
if you still havent found the solution, can you post like 10 rows of sample data, and pls specify what your criteria should be.
 
Upvote 0
raccoon588 said:
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?
Click to expand...

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.
 
Upvote 0
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.

NameDeptShiftAreaPositionBadge NumberHire Date
Bezio, Wanda3721SSLLamp Maker10718November 26, 1984
Boston, Nick2051SSLLamp Maker15830August 8, 2016
Burgess, Ross3511SSLLamp Maker77055January 15, 2018
Chase, Brittany2051SSLLamp Maker15801January 18, 2016
Collelo, Alex3721SSLLamp Maker77059January 29, 2018
Ervin, Lorelei3721SSLLamp Maker15781August 3, 2015
Furlong, Ross3011SSLMechanic15477May 22, 2000
Gerini, Josh3511SSLLamp Maker15746May 19, 2014
Goudreault, Tammy3721SSLLamp Maker15761January 19, 2015
Kelley, Lia3721SSLLamp Maker15795November 23, 2015
Lawson, Heather3511SSLLamp Maker15778July 27, 2015
Mackey, LeeAnn3011SSLLamp Maker15548January 2, 2001
McLemore, Sandy2011SSLLamp Maker15029May 17, 1993
Mitchell, Robyn1021SSLMaterial Handler15357August 30, 1999
Moy, Jennifer3011SSLLamp Maker15789November 2, 2015
Ouellette, Courtney2051SSLLamp Maker77015July 10, 2017
Pelchat, Linda1021SSLLamp Maker\GL15794November 23, 2015
Pelchat, Randy2051SSLMechanic15507September 5, 2000
Richards, Tina3721SSLLamp Maker15737March 3, 2014
Rockwell, Tyler2051SSLLamp Maker77050December 11, 2017
Roth, Jen3011SSLLamp Maker11024February 3, 1986
Traxler, Kimberly3721SSLLamp Maker\GL76957February 20, 2017

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0

Excel 2016 (Windows) 32 bit
ABCDEFGHI
1NameDeptShiftAreaPositionBadge NumberHire Date
2Bezio, Wanda3721SSLLamp Maker1071826-Nov-84Boston, Nick
3Boston, Nick2051SSLLamp Maker158308-Aug-16Chase, Brittany
4Burgess, Ross3511SSLLamp Maker7705515-Jan-18Ouellette, Courtney
5Chase, Brittany2051SSLLamp Maker1580118-Jan-16Rockwell, Tyler
6Collelo, Alex3721SSLLamp Maker7705929-Jan-18
7Ervin, Lorelei3721SSLLamp Maker157813-Aug-15
8Furlong, Ross3011SSLMechanic1547722-May-00
9Gerini, Josh3511SSLLamp Maker1574619-May-14
10Goudreault, Tammy3721SSLLamp Maker1576119-Jan-15
11Kelley, Lia3721SSLLamp Maker1579523-Nov-15
12Lawson, Heather3511SSLLamp Maker1577827-Jul-15
13Mackey, LeeAnn3011SSLLamp Maker155482-Jan-01
14McLemore, Sandy2011SSLLamp Maker1502917-May-93
15Mitchell, Robyn1021SSLMaterial Handler1535730-Aug-99
16Moy, Jennifer3011SSLLamp Maker157892-Nov-15
17Ouellette, Courtney2051SSLLamp Maker7701510-Jul-17
18Pelchat, Linda1021SSLLamp Maker\GL1579423-Nov-15
19Pelchat, Randy2051SSLMechanic155075-Sep-00
20Richards, Tina3721SSLLamp Maker157373-Mar-14
21Rockwell, Tyler2051SSLLamp Maker7705011-Dec-17
22Roth, Jen3011SSLLamp Maker110243-Feb-86
23Traxler, Kimberly3721SSLLamp Maker\GL7695720-Feb-17
Sheet4
Cell Formulas
RangeFormula
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.
 
Last edited:
Upvote 0

Excel 2016 (Windows) 32 bit
ABCDEFGHI
1NameDeptShiftAreaPositionBadge NumberHire Date
2Bezio, Wanda3721SSLLamp Maker1071826-Nov-84Boston, Nick
3Boston, Nick2051SSLLamp Maker158308-Aug-16Chase, Brittany
4Burgess, Ross3511SSLLamp Maker7705515-Jan-18Ouellette, Courtney
5Chase, Brittany2051SSLLamp Maker1580118-Jan-16Rockwell, Tyler
6Collelo, Alex3721SSLLamp Maker7705929-Jan-18
7Ervin, Lorelei3721SSLLamp Maker157813-Aug-15
8Furlong, Ross3011SSLMechanic1547722-May-00
9Gerini, Josh3511SSLLamp Maker1574619-May-14
10Goudreault, Tammy3721SSLLamp Maker1576119-Jan-15
11Kelley, Lia3721SSLLamp Maker1579523-Nov-15
12Lawson, Heather3511SSLLamp Maker1577827-Jul-15
13Mackey, LeeAnn3011SSLLamp Maker155482-Jan-01
14McLemore, Sandy2011SSLLamp Maker1502917-May-93
15Mitchell, Robyn1021SSLMaterial Handler1535730-Aug-99
16Moy, Jennifer3011SSLLamp Maker157892-Nov-15
17Ouellette, Courtney2051SSLLamp Maker7701510-Jul-17
18Pelchat, Linda1021SSLLamp Maker\GL1579423-Nov-15
19Pelchat, Randy2051SSLMechanic155075-Sep-00
20Richards, Tina3721SSLLamp Maker157373-Mar-14
21Rockwell, Tyler2051SSLLamp Maker7705011-Dec-17
22Roth, Jen3011SSLLamp Maker110243-Feb-86
23Traxler, Kimberly3721SSLLamp Maker\GL7695720-Feb-17
Sheet4
Cell Formulas
RangeFormula
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
 
Last edited:
Upvote 0
Just adjust any formula's refrences to the size of your data, and drag down your formula as much as u want...
 
Upvote 0
raccoon588 said:
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.

[...]
Click to expand...

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))))
 
Upvote 0
Aladin Akyurek said:
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))))
Click to expand...

Yes, that way you can change the criteria.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
INDEX Functions with multiple criteria
Replies
5
Views
264
Phuoc
P
T
  • Question
Pull data from multiple sheets that match varying criteria
Replies
0
Views
88
T1991
T
C
  • Question
Index/Match | Index/Small Formula
Replies
5
Views
366
Eric W
Eric W
H
  • Question
Excel Formula
Replies
0
Views
114
hrayani
H
G
  • Question
Moving the formula to the next column
Replies
7
Views
124
Geo Jul
G

Forum statistics

Threads
1,215,631
Messages
6,125,905
Members
449,273
Latest member
mrcsbenson

Share this page

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