Help with Index using Aggregate with multiple criteria

JacksonKee

New Member
Joined
Apr 24, 2023
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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 TitleDEPTQS ReviewerFront line Team MemberTeam MemberAMRM Team MemberTraining / Onboarding / KM Core TeamAdvisor Under Letter AgreementManagement Team MemberLeadership Team MemberCharged Project During Past 60 daysStatus
xAmy CPhysical Scientist, Sr.xActive
xAlexandra ZResearch Assistant, IxActive
xAlexa KimberlyPolicy Analyst IIIxActive
xAlba S. P.Research Project Specialist, IxActive
xAmara CEngineer, AdjunctxActive
xAnushara YResearch Project Specialist, IVxActive
xAshley TomPolicy Analyst, III, AdjunctxActive
xBeckky EPolicy Researcher, SeniorxActive
xBenny TPolicy Researcher, FullxActive
xBeverly OResearch Project Specialist, IxActive
xBrian VDefense Analyst, IVxActive
xBriam KrisQuantitative Analyst, IVxxxActive
xBrandon CPolicy Researcher, AssocxActive
xCarlos G. M.Physician Policy Researcher, FullxxActive
xCharlie GrossCommunications Analyst, IVxActive
xChristina M.DPolicy Researcher, FullxActive
xChristopher BPolicy Researcher, FullxActive
xChristina CPolitical Scientist, Sr.xActive
xStoryResearch Programmer, IIIxActive
xOarry IPhysician Policy Researcher, Sr.xActive
xDaniel NPolicy Researcher, AssocxActive
xDavid GanzPhysician Policy Researcher, AdjxActive
xDebbie IPhysician Policy Researcher, AdjxActive
xDestini HResearch Project Specialist, IIIxInactive
xDelvin TResearch Programmer, IxActive
xColleen YoungTechnical Analyst, IIxxActive
xHonchPolicy Analyst, IVxActive
xErica ChenQuantitative Analyst, IIIxActive
xLancaseteraPolicy Researcher, Sr.xActive
xLockwoodResearch Project Specialist, IIxActive
xSmithAdministrative Assistant, IIIxActive
x CehinePolicy Researcher, Sr.xActive
xHaque IsaResearch Project Specialist, IIxActive
xArciniegas REconomist, SrxActive
x HastingsPolicy Analyst, IVxActive
x RingelEconomist, SrxActive
xJalen EscobarResearch Project Specialist, IIxxActive
xJessica OOperations Researcher, Sr.xxActive
xJoddy LPolicy Researcher, AssocxActive
xJoshua Lou
xJoshua C
xJoey LBehavioral/Social Scientist, Sr.xInactive
xJohnPolicy Researcher, AssocxActive
xJonathan CPolicy Researcher, FullxActive
xJonathan CPolicy Analyst, IIxActive
xDawsonPhysical Scientist, AsscxActive
xJ NewellResearch Software Engineer, IIIxActive
xJ LeeResearch Programmer, IIxActive
xJustin HummerBehavioral/Social Scientist, AssocxActive
xJ SheetyPhysician Policy Researcher, FullxxActive
xDillmanResearch Operations Administrator, IxActive
xKatherine CarmanEconomist, SrxActive
xKatie BristolResearch Project Specialist, IxActive
xKyle WPolicy Researcher, Sr.xActive
xK WilleyEngineer, Sr.xActive
xKelly EResearch Project Specialist, IIxActive
xKiberlyResearch Project Specialist, IIxxActive
x Scott Physician Policy Researcher, AdjActive
xKrisResearch Project Specialist, IIxActive
xGaleBehavioral/Social Scientist, SrxxActive
xChristophe SereAdministrative Assistant, IVxActive
xKely-EvansEngineer, SrxActive
xLawrence FPhysician Policy Researcher, FullxActive
xMichelle PfeiferEditorial AssiatantxActive
xLiam PainePolicy Analyst, IIxActive
xLisa SerranoResearch Project Specialist, IIxActive
xMeredithBehavioral/Social Scientist, Sr.xActive
xKen NguyenPhysical Scientist, FullxActive
xLance N. APhysician Policy Researcher, Assoc.xActive
xMaxine DBehavioral/Social Scientist, Sr.xActive
xAbir M.Physician Policy Researcher, Sr.no longer?Active
xWeden Sx
xVega DexResearch Editor, IxActive
x Laura FPhysician Policy Researcher, Sr.xActive
xBecky KResearch Project Specialist, IIIxActive
xMark HansonQuantitative Analyst, IVxxActive
xMarygail BraunerOperations Researcher, AdjunctxActive
xMaxine R.YAdministrative Assistant, IVxActive
xAndrew K.OBehavioral/Social Scientist, FullxActive
xBeckBehavioral/Social Scientist, FullxActive
xLisa SResearch Assistant, AdjunctxActive
xPolicy Researcher, Sr.xActive
xResearch Assistant, IActive
xBeyeneEngineer, SrxActive
xHuertaResearch Programmer, IxActive
xErnecoffPolicy Researcher, Assoc.Active
xTena PResearch Project Specialist, IIIxActive
xBrener QManagement Scientist, Sr.xActive
x BriscombePhysician Policy Researcher, FullxActive
xPeterEngineer, Sr.xActive
x RaoalEconomist, FullxActive
x VardMathematician, Sr.xxActive
xAli RameyTechnical Analyst, IIIxActive
xSepich CAdministrative Assistant, IVxActive
xKasey PPhysician Policy Researcher, AdjxActive
xRusahanaTechnical Analyst, II
xSamuel HResearch Project Specialist, IxActive
xSlawnikowskiAdministrative Assistant, IIxActive
xDuhachekPhysical Scientist, Assoc.xActive
xMalvoyPolicy Analyst, VxActive
xScott StephensonxActive
xShahzeb KhanResearch Project Specialist, IIxActive
xShakeela Jackson
xShareeka Abdul MohaiminResearch Project Specialist, IxActive
xShelly CulbertsonPolicy Researcher, Sr.xActive
xShira FischerPhysician Policy Researcher, FullxxActive
xSusan CatalanoAdministrative Assistant VxActive
xSusan GatesEconomist, SrxActive
xSuzanne GencPhysical Scientist, FullxxActive
xSydney VolpeResearch Project Specialist, IxActive
xTalia ShapiroResearch Project Specialist, IIxActive
xTerry KellyMathematician Principal, Sr.xActive
xTim ConleyOperations Researcher, Sr.xActive
xTim GuldenPolicy Researcher, SrxxActive
xTom LaTourettePhysical Scientist, Sr.xActive
xTom WebsterResearch Project Specialist, IIxxActive
xTom Concannon
xWenny GBehavioral/Social Scientist, AssocxActive
xWesley JOperations Researcher, AdjunctxActive
xNham PPolicy Analyst, AdjxActive
xAbduala FResearch Project Specialist, IIxActive
xDarmaResearch Project Specialist, IIIxInactive
Alyssa Pozniakx
Chad Coganx
Christina Sx
Christopher Sx
Daniella Mx
Laurne B
Erica Lx
Ericson Ix
Jeff Px
Jill Sx
Kamon Mx
Katie Fx
Marc Ex
Talia S
Harry Mx
Happy N. Mx
Mary TActive
 
You're welcome.
So, this did what you wanted without blanks?
 
Upvote 1

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
@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 CecchineGary Cecchine
Ishrat HaqueIshrat Haque
Ismael Arciniegas RuedaIsmael Arciniegas Rueda
Jaime HastingsJaime Hastings
Jeanne RingelJeanne Ringel
Jennifer EscobarJennifer Escobar
Jessie RiposoJessie Riposo
Joachim HeroJoachim Hero
Jodi Liu
Joe Hero
Joie Acosta
Jon LevinJon Levin
Jonathan CantorJonathan Cantor
Jonathan ChamJonathan Cham
Joseph DawsonJoseph Dawson
Julie NewellJulie Newell
Justin LeeJustin Lee
 
Upvote 0
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.

Excel Formula:
=FILTER(ALL!L2:L2000,(ALL!J2:J2000="x")*(ALL!X2:X2000="Active")*(ALL!L2:L2000<>""),"")
 
Upvote 0
Solution
You're welcome. Glad we could help. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,847
Members
452,361
Latest member
d3ad3y3

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