CODEWITHSUNDEEP
excelexcel-formula
QuadrilateralMom
QuadrilateralMom

Reputation: 45

INDEX, MATCH - find first 2 instances of match and return the row above

I'm trying to check a column for an exact name (in my pictured example, JonP and not JonF) and return the time that appears in the row above the name. I have been able to figure out how to return the first instance by using this formula =IFERROR(INDEX(A$2:A$9,MATCH("*"&$D2&"*",A$2:A$9,0)-1,1),"")

What I want to do is find both instances of JonP and then use TEXTJOIN to list the times that are above each instance of JohnP.

I've looked at the examples on the below 2 pages.

  1. https://trumpexcel.com/lookup-second-value/
  2. https://www.get-digital-help.com/index-match-multiple-results/ example ...but my understanding of how the other functions (SMALL, ISNUMBER,ROW, COLUMN) are interacting in those examples is too tenuous for me to figure out how to apply it to my situation, which is different, since I'm only matching within in 1 column (vs between the 2 different columns in the examples) and want to return the value in the row above the match.

---EDIT--- Here is a picture of the final solution, after realizing I needed to account for instances in which the string I was searching for was mixed with other names. =TEXTJOIN(", ",TRUE,FILTER(Schedule[Schedule],ISNUMBER(SEARCH(D2,OFFSET(Schedule[Schedule],1,0)))))

corrected example

Upvotes: 0

Views: 183

Answers (1)

Ike
Ike

Reputation: 13024

You can use this formula:

=LET(Schedules,DROP(VSTACK("",Table1[Schedule]),-1),
 TEXTJOIN(", ",TRUE,FILTER(Schedules,Table1[Schedule]=C2)))

It first creates an offset of the schedule (down one).

Like that it could be used to filter the schedule-table.

If it helps for visualisation try this:

=LET(Schedules,DROP(VSTACK("",Table1[Schedule]),-1),
HSTACK(Table1[Schedule],Schedules))

enter image description here

----- EDIT --- Solution w/o VSTACK

=TEXTJOIN(", ",TRUE,FILTER(Table1[Schedule],OFFSET(Table1[Schedule],1,0)=C2))

Upvotes: 1

Related Questions