CODEWITHSUNDEEP
excelindexingmatch
Pavlo Kobzar
Pavlo Kobzar

Reputation: 27

Index/Match to return a list of results matching criteria

I have an array of values (hours) that are assigned to jobs for every day of the month.

I would like to build a schedule for the day - a list of job numbers that need to be done today.

@Scott Craner helped to build the reverse tool - it returns a date for a job in question.

search jobs

=INDEX(B2:E2,MATCH(TRUE,INDEX(INDEX(B3:E9,MATCH(G3,A3:A9,),)<>"",),0))

Upvotes: 0

Views: 574

Answers (1)

Scott Craner
Scott Craner

Reputation: 152525

If on has the new dynamic array formula Filter put this in H4 and Excel will spill down the results:

=FILTER(A3:A9,INDEX(B3:E9,,MATCH(H2,B2:E2,0))<>"")

If not then we need to get a little more creative. Put this in H4 and copy down:

=IFERROR(INDEX(A:A,AGGREGATE(15,7,ROW($A$3:$A$9)/(INDEX($B$3:$E$9,,MATCH($H$2,$B$2:$E$2,0))<>""),ROW(A1))),"")

Upvotes: 1

Related Questions