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

 

[awoohaw]

@JacksonKee , welcome to the forum.
Which of above are what worksheets and what are the column letters? There is an add in (xl2bb, link below) that allows you to copy mini workbooks of data that include the formulas and row/column headers, and sheet names. Please help the forum help you.

In the above which column headers that you have above apply to the column letters you mention.

 

[JacksonKee]

Thank you for responding. Yes I was trying to upload it but having some trouble.
This worksheet displayed is ALL! and the names in (Column L) I would like to auto populate to another worksheet called RAND! in Column A. The criteria is those with an "x" in column J and those with and "Active" status in Column X. I was able to solve for "x" using this formula =INDEX(ALL!L:L,AGGREGATE(15,6,ROW(ALL!$J$1:$J$2000)/(ALL!$J$1:$J$2000="x"),ROW(1:1))) ...... I haven't had any luck trying to add another criteria.

 

[awoohaw]

ok. well, keep trying.
You still did not give the associated column names with the column letters in your description.

 

[AhoyNC]

See the attached link which will show how to us the INDEX / AGGREGATE functions to return multiple cells.

However since you have Excel 365 you should be able to us the FILTER function.

 

[JacksonKee]

I'm sorry @awoohaw , Thank you
I've including the names of the columns. For some reason my Excel isn't giving me access to use xL2bb


This worksheet displayed is ALL! and the names in Column L=First+Last I would like to auto populate to another worksheet called RAND! in Column A. The criteria is those with an "x" in column J=Staff (Including Adjunct) and those with and "Active" status in Column X=Status. I was able to solve for "x" using this formula =INDEX(ALL!L:L,AGGREGATE(15,6,ROW(ALL!$J$1:$J$2000)/(ALL!$J$1:$J$2000="x"),ROW(1:1))) ...... I haven't had any luck trying to add another criteria.

 

[AhoyNC]

Try something like this:

Book1
	A
1	Names
2	Name1
3	Name7
4	Name10
Rand

Cell Formulas
Range	Formula
A2:A4	A2	=FILTER(ALL!L2:L11,(ALL!J2:J11="x")*(ALL!X2:X11="Active"),"")
Dynamic array formulas.

Book1
	I	J	K	L	M										W	X
1		Staff		Names												Status
2		x		Name1												Active
3				Name2												Active
4		x		Name3
5				Name4												Active
6				Name5
7				Name6
8		x		Name7												Active
9				Name8
10				Name9
11		x		Name10												Active
ALL

 

[JacksonKee]

Hello @AhoyNC

I didn't have any luck using that formula

 

[AhoyNC]

Did you get an error??? Did you change ranges to match your data?

 

[JacksonKee]

Hi @AhoyNC I tried it again. It works, I needed to update!! thank you. I will keep this formula in my arsenal.

I was trying to create a formula that wouldn't leave any blanks in the list if they didn't meet the two criteria but just display those that did. And when I update the workbook ALL! it would update the workbook RAND!