Is it possible to get a particular row cell value using a formula that combines both row and column searches?

1
votes

I am setting up a shift roster. The top row is sequential dates, and the leftmost rows are staff names. A staff member can look along their row to see which shifts they are on for the coming weeks.

I want to automatically pull out the allocated shifts into another sheet to show a dynamic "Today's Staffing" which shows who is on duty in each role for the day e.g. for column TODAY'S DATE find which row contains MORNING SHIFT and return the FIRST COLUMN FOR THAT ROW which should contain the name.

I have access to both MS Excel and Google Sheets.

Is there a function/way that I can do this?

Example google sheet:

https://docs.google.com/spreadsheets/d/1VTYK39xuHT0-4s8O5398dnseXYsE0q54-os-rJNNVB8/edit?usp=sharing

1
google-sheetsgoogle-sheets-formula
updated original post – user3378717

1 Answers

1
votes
=QUERY({INDIRECT(ADDRESS(2, MATCH(TODAY(), A1:1, 0), 4)&":"&
      SUBSTITUTE(ADDRESS(1, MATCH(TODAY(), A1:1, 0), 4), 1, )), A2:A}, 
 "where Col1 <>'OFF' and Col1 <>''")

0

if you want to run this under with just 3 people do:

=QUERY({INDIRECT(ADDRESS(2, MATCH(TODAY(), Sheet1!A1:1, 0), 4)&":"&
      SUBSTITUTE(ADDRESS(4, MATCH(TODAY(), Sheet1!A1:1, 0), 4), 1, )), Sheet1!A2:A4}, 
 "where Col1 <>'OFF' and Col1 <>'' order by Col1 desc")

enter image description here