How do I generate the location (row and column) of a specific value in Google Sheets?-CodePudding

I made a schedule in Google Sheets (days of the week as columns, time as rows, student names within the chart). I want to make an additional chart restating the data by student name that will update if I move the students in chart 1. I would love it to report day and time, but would settle for any sort of location information whatsoever.

I have tried combinations of VLOOKUP, QUERY, and Match. The problem is that it only works as long as the student stays in the same location. If I move it to a different cell I get an error as it only works for a single row or column.

The closest I have gotten is that I can make a hyperlink to the cell. When you hover over the link it gives the cell location, but I don't know if it is possible to make the cell location the name.

CodePudding user response：

Use this formula

=ArrayFormula( 
 SORTN(SPLIT(FLATTEN(Schedule!B1:F1&"^_~"&Schedule!B2:F6&"^_~"&Schedule!A2:A6),"^_~"),
 COUNTA( INDEX(SPLIT(FLATTEN(Schedule!B1:F1&"^_~"&Schedule!B2:F6&"^_~"&Schedule!A2:A6),"^_~"), ,3)),,3,1))