Thread: [Solved] Index Match Formula to return value from a list with criteria
View Single Post
 
Old 10-26-2018, 05:01 AM
NBVC's Avatar
NBVC NBVC is offline Windows 10 Office 2013
The Formula Guy
  
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default
Do these work?


in AM16 of June sheet:


=IFERROR(INDEX(REMARKS,MATCH(1,INDEX((FULL_NAME=$B 16)*(tDATA[DATE OF TRANSFER OUT/DROP OUT]<=$AC$6),0),0)),"")


copied down


In B16 of July sheet:


=IFERROR(INDEX(FULL_NAME,MATCH(SMALL(IF((COUNTIF($ B$15:$B15,FULL_NAME)=0)*(GRADE=$AC$8)*(SECTION=$AI $8)*(DATE_ENROLLED<=AC$6)*((tDATA[DATE OF TRANSFER OUT/DROP OUT]="")+(tDATA[DATE OF TRANSFER OUT/DROP OUT]>EOMONTH(AC$6,-1)))*(SEX="M"),COUNTIF(FULL_NAME,"<"&FULL_NAME),"" ),1),COUNTIF(FULL_NAME,"<"&FULL_NAME),0)),"")


confirmed with CTRL+SHIFT+ENTER and copied down
Reply With Quote