Help with Index using Aggregate with multiple criteria

[JacksonKee]

Hello,
I want names for the ALL! worksheet (Column L) to auto populate in the RAND! worksheet based on them being Active (Column X) and those with in "X") in Column J. I was able to make it work for 1 of the criteria for "x" in column J ...... =INDEX(ALL!L:L,AGGREGATE(15,6,ROW(ALL!$J$1:$J$2000)/(ALL!$J$1:$J$2000="x"),ROW(1:1)))

any help with be appreciated

Staff (including Adjunct)

First + Last

Title

DEPT

QS Reviewer

Front line Team Member

Team Member

AMRM Team Member

Training / Onboarding / KM Core Team

Advisor Under Letter Agreement

Management Team Member

Leadership Team Member

Charged Project During Past 60 days

Status

x

Amy C

Physical Scientist, Sr.

x

Active

x

Alexandra Z

Research Assistant, I

x

Active

x

Alexa Kimberly

Policy Analyst III

x

Active

x

Alba S. P.

Research Project Specialist, I

x

Active

x

Amara C

Engineer, Adjunct

x

Active

x

Anushara Y

Research Project Specialist, IV

x

Active

x

Ashley Tom

Policy Analyst, III, Adjunct

x

Active

x

Beckky E

Policy Researcher, Senior

x

Active

x

Benny T

Policy Researcher, Full

x

Active

x

Beverly O

Research Project Specialist, I

x

Active

x

Brian V

Defense Analyst, IV

x

Active

x

Briam Kris

Quantitative Analyst, IV

x

x

x

Active

x

Brandon C

Policy Researcher, Assoc

x

Active

x

Carlos G. M.

Physician Policy Researcher, Full

x

x

Active

x

Charlie Gross

Communications Analyst, IV

x

Active

x

Christina M.D

Policy Researcher, Full

x

Active

x

Christopher B

Policy Researcher, Full

x

Active

x

Christina C

Political Scientist, Sr.

x

Active

x

Story

Research Programmer, III

x

Active

x

Oarry I

Physician Policy Researcher, Sr.

x

Active

x

Daniel N

Policy Researcher, Assoc

x

Active

x

David Ganz

Physician Policy Researcher, Adj

x

Active

x

Debbie I

Physician Policy Researcher, Adj

x

Active

x

Destini H

Research Project Specialist, III

x

Inactive

x

Delvin T

Research Programmer, I

x

Active

x

Colleen Young

Technical Analyst, II

x

x

Active

x

Honch

Policy Analyst, IV

x

Active

x

Erica Chen

Quantitative Analyst, III

x

Active

x

Lancasetera

Policy Researcher, Sr.

x

Active

x

Lockwood

Research Project Specialist, II

x

Active

x

Smith

Administrative Assistant, III

x

Active

x

Cehine

Policy Researcher, Sr.

x

Active

x

Haque Isa

Research Project Specialist, II

x

Active

x

Arciniegas R

Economist, Sr

x

Active

x

Hastings

Policy Analyst, IV

x

Active

x

Ringel

Economist, Sr

x

Active

x

Jalen Escobar

Research Project Specialist, II

x

x

Active

x

Jessica O

Operations Researcher, Sr.

x

x

Active

x

Joddy L

Policy Researcher, Assoc

x

Active

x

Joshua Lou

x

Joshua C

x

Joey L

Behavioral/Social Scientist, Sr.

x

Inactive

x

John

Policy Researcher, Assoc

x

Active

x

Jonathan C

Policy Researcher, Full

x

Active

x

Jonathan C

Policy Analyst, II

x

Active

x

Dawson

Physical Scientist, Assc

x

Active

x

J Newell

Research Software Engineer, III

x

Active

x

J Lee

Research Programmer, II

x

Active

x

Justin Hummer

Behavioral/Social Scientist, Assoc

x

Active

x

J Sheety

Physician Policy Researcher, Full

x

x

Active

x

Dillman

Research Operations Administrator, I

x

Active

x

Katherine Carman

Economist, Sr

x

Active

x

Katie Bristol

Research Project Specialist, I

x

Active

x

Kyle W

Policy Researcher, Sr.

x

Active

x

K Willey

Engineer, Sr.

x

Active

x

Kelly E

Research Project Specialist, II

x

Active

x

Kiberly

Research Project Specialist, II

x

x

Active

x

Scott

Physician Policy Researcher, Adj

Active

x

Kris

Research Project Specialist, II

x

Active

x

Gale

Behavioral/Social Scientist, Sr

x

x

Active

x

Christophe Sere

Administrative Assistant, IV

x

Active

x

Kely-Evans

Engineer, Sr

x

Active

x

Lawrence F

Physician Policy Researcher, Full

x

Active

x

Michelle Pfeifer

Editorial Assiatant

x

Active

x

Liam Paine

Policy Analyst, II

x

Active

x

Lisa Serrano

Research Project Specialist, II

x

Active

x

Meredith

Behavioral/Social Scientist, Sr.

x

Active

x

Ken Nguyen

Physical Scientist, Full

x

Active

x

Lance N. A

Physician Policy Researcher, Assoc.

x

Active

x

Maxine D

Behavioral/Social Scientist, Sr.

x

Active

x

Abir M.

Physician Policy Researcher, Sr.

no longer?

Active

x

Weden S

x

x

Vega Dex

Research Editor, I

x

Active

x

Laura F

Physician Policy Researcher, Sr.

x

Active

x

Becky K

Research Project Specialist, III

x

Active

x

Mark Hanson

Quantitative Analyst, IV

x

x

Active

x

Marygail Brauner

Operations Researcher, Adjunct

x

Active

x

Maxine R.Y

Administrative Assistant, IV

x

Active

x

Andrew K.O

Behavioral/Social Scientist, Full

x

Active

x

Beck

Behavioral/Social Scientist, Full

x

Active

x

Lisa S

Research Assistant, Adjunct

x

Active

x

Policy Researcher, Sr.

x

Active

x

Research Assistant, I

Active

x

Beyene

Engineer, Sr

x

Active

x

Huerta

Research Programmer, I

x

Active

x

Ernecoff

Policy Researcher, Assoc.

Active

x

Tena P

Research Project Specialist, III

x

Active

x

Brener Q

Management Scientist, Sr.

x

Active

x

Briscombe

Physician Policy Researcher, Full

x

Active

x

Peter

Engineer, Sr.

x

Active

x

Raoal

Economist, Full

x

Active

x

Vard

Mathematician, Sr.

x

x

Active

x

Ali Ramey

Technical Analyst, III

x

Active

x

Sepich C

Administrative Assistant, IV

x

Active

x

Kasey P

Physician Policy Researcher, Adj

x

Active

x

Rusahana

Technical Analyst, II

x

Samuel H

Research Project Specialist, I

x

Active

x

Slawnikowski

Administrative Assistant, II

x

Active

x

Duhachek

Physical Scientist, Assoc.

x

Active

x

Malvoy

Policy Analyst, V

x

Active

x

Scott Stephenson

x

Active

x

Shahzeb Khan

Research Project Specialist, II

x

Active

x

Shakeela Jackson

x

Shareeka Abdul Mohaimin

Research Project Specialist, I

x

Active

x

Shelly Culbertson

Policy Researcher, Sr.

x

Active

x

Shira Fischer

Physician Policy Researcher, Full

x

x

Active

x

Susan Catalano

Administrative Assistant V

x

Active

x

Susan Gates

Economist, Sr

x

Active

x

Suzanne Genc

Physical Scientist, Full

x

x

Active

x

Sydney Volpe

Research Project Specialist, I

x

Active

x

Talia Shapiro

Research Project Specialist, II

x

Active

x

Terry Kelly

Mathematician Principal, Sr.

x

Active

x

Tim Conley

Operations Researcher, Sr.

x

Active

x

Tim Gulden

Policy Researcher, Sr

x

x

Active

x

Tom LaTourette

Physical Scientist, Sr.

x

Active

x

Tom Webster

Research Project Specialist, II

x

x

Active

x

Tom Concannon

x

Wenny G

Behavioral/Social Scientist, Assoc

x

Active

x

Wesley J

Operations Researcher, Adjunct

x

Active

x

Nham P

Policy Analyst, Adj

x

Active

x

Abduala F

Research Project Specialist, II

x

Active

x

Darma

Research Project Specialist, III

x

Inactive

Alyssa Pozniak

x

Chad Cogan

x

Christina S

x

Christopher S

x

Daniella M

x

Laurne B

Erica L

x

Ericson I

x

Jeff P

x

Jill S

x

Kamon M

x

Katie F

x

Marc E

x

Talia S

Harry M

x

Happy N. M

x

Mary T

Active

 

[AhoyNC]

You're welcome.
So, this did what you wanted without blanks?

 

[JacksonKee]

@AhoyNC

It created what I wanted with blanks. The first column is the orginal formual =INDEX(ALL!L:L,AGGREGATE(15,6,ROW(ALL!$J$1:$J$2000)/(ALL!$J$1:$J$2000="x"),ROW(2:2)))
and the second column is the new one you provided. =FILTER(ALL!L2:L2,(ALL!J2:J2="x")*(ALL!X2:X2="Active"),"").

Gary Cecchine	Gary Cecchine
Ishrat Haque	Ishrat Haque
Ismael Arciniegas Rueda	Ismael Arciniegas Rueda
Jaime Hastings	Jaime Hastings
Jeanne Ringel	Jeanne Ringel
Jennifer Escobar	Jennifer Escobar
Jessie Riposo	Jessie Riposo
Joachim Hero	Joachim Hero
Jodi Liu
Joe Hero
Joie Acosta
Jon Levin	Jon Levin
Jonathan Cantor	Jonathan Cantor
Jonathan Cham	Jonathan Cham
Joseph Dawson	Joseph Dawson
Julie Newell	Julie Newell
Justin Lee	Justin Lee

 

[Peter_SSs]

I was able to make it work for 1 of the criteria for "x" in column J ...... =INDEX(ALL!L:L,AGGREGATE(15,6,ROW(ALL!$J$1:$J$2000)/(ALL!$J$1:$J$2000="x"),ROW(1:1)))

You say that you do not want rows that are blank in column L returned and that the above formula works for one criteria. However, that formula also returns a result when the criteria is met and column L is blank. For example, with your original sample data there are two rows just below "Lisa S" (at about row 82) that are blank in column L and have an "x" in column J. Your formula above returns a result (0) for those rows.

To exclude blank rows as well, try this one.

=FILTER(ALL!L2:L2000,(ALL!J2:J2000="x")*(ALL!X2:X2000="Active")*(ALL!L2:L2000<>""),"")

 

[JacksonKee]

Thank you so much @Peter_SSs and @AhoyNC !!
That did it

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.