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

SoS | Dan Palenchar

What is your desired return value for "Visit Label in Row 1." Does Visit Label have its own column, are there several, etc.? I am trying to determine what constitutes the last "Visit Label".

👨🏼‍💻 Dan Palenchar | School of Sheets Solutions Consulting (Smartsheet Aligned Gold Partner)

If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and/or ❤️Awesome).

🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus

▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube

KASmotion

@Dan Palenchar my desired return value can be in multiple columns, but it will always be the header for that visit that's in blue in Row 1.

E.g., for patient -001, the last MRI date was 8/14/23, I'd want the return value to be "Cycle 3 Date" aka 5th column/1st row in the range. For patient -002, the last MRI date was 4/3/23, so I'd want the return value to be "EOT Date" aka 7th column/1st row. Let me know if this makes sense or not !

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

KASmotion

@HeatherD. both formulas worked perfectly, thank you so much !!! The missing piece for me was incorporating the '$' :) Super appreciate your help !!