INDEX/MATCH - single sheet, header row

KASmotion

Hi, I could use some help putting into practice some of the INDEX/MATCH trickery I learned at Engage 😅

I have a tracker that tracks patient MRI visits - the tracker is heady, with multiple (~30-35) different date columns for different visits, interspersed with dropdown columns and column formulas with projected visit dates (the format is Projected [Visit X] Date column - Actual [Visit X] Date column - [Visit X] Status column). The visits do not follow a continuous format (i.e., it's not just Visit 1, Visit 2, Visit 3, there are visits with names that break the pattern).

I am ultimately trying to pull the most recent MRI date, visit, and status, into a different metrics sheet. I was able to get the Last MRI Date to work with a MAX() function, but now I want to get the MRI Visit label that corresponds with the Last MRI Date. I know I can't use column names in a formula, so I added the visit labels into Row 1.

I used this formula, and was able to get it to return the column number : =MATCH([Last MRI Date]@row, [Cycle 1 Date]@row:[End of Treatment]@row, 0). But I don't know from here how to build the INDEX/MATCH (or INDEX/MATCH/MATCH ?) to return the Visit Label in Row 1 that corresponds to the Last MRI Date. I've attached a screenshot with test data that follows the same structure

Many thanks in advance for the help !!!

HeatherD.

@KASmotion See if this will work for your Last MRI Visit: =INDEX([Cycle 1 Date]$1:[Cycle 3 Status]$1, 1, MATCH([Last MRI Date]@row, [Cycle 1 Date]@row:[Cycle 3 Status]@row, 0))

Of course, be sure my column names match yours. To explain the formula: We're telling it to pull in the labels from row 1 [Cycle 1 Date]$1:[Cycle 3 Status]$1. In the row_index position where you would typically see the first MATCH function (hello "row labels" from the INDEX/MATCH/MATCH trickery!), we just have the number 1 - because we only need row 1. In the column_index position, we indeed have a MATCH, which is telling it how to select the column (based on the Last MRI Date).

If you haven't figured out the Last MRI Status column yet, I found a one that would work:

=INDEX([Cycle 1 Date]@row:[Last MRI Date]@row, 1, MATCH([Last MRI Date]@row, [Cycle 1 Date]@row:[Last MRI Date]@row, 0) + 1)

It's pulling in an INDEX/MATCH, telling it (with the 1 in the row_index position) to select the current row, but the +1 within the MATCH (in the column_index position) tells it to bring back the value from the column next to the column where it finds the Last MRI date in the current row.

Hope this helps!

Best,

Heather