google-sheetsgoogle-sheets-formulagoogle-sheets-querygs-vlookupimportrange

Get value from another google sheet

  1. The formula should be placed in C7: ={"SCHEDULE";ARRAYFORMULA()}
  2. Need a formula that will search the name of the user from INDIRECT(J6) and find the Time from another sheet (1) depending on the Sched sheet name in cell B3 and (2) based on what day is selected in INDIRECT(J5)
  3. If the user has no schedule yet (blank on the other sheet, it should return "NONE YET".
  4. Basically, we'll get the user's schedule from the other sheet...

main sheet: https://docs.google.com/spreadsheets/d/1c3HdMq4PA50pYr88JqPoG51jvru8ipp8ebe4z5DczTQ/edit?usp=sharing

other sheet that has the schedules: https://docs.google.com/spreadsheets/d/1LM7yIJJ_w6mftXJhQgonLy67w6CUooU04WgdwG9k3xo/edit?usp=sharing

Solution

  • use:

    ={"SCHEDULE"; ARRAYFORMULA(IF(INDIRECT(J6)="",,IFNA(VLOOKUP(INDIRECT(J6), 
 IMPORTRANGE("1LM7yIJJ_w6mftXJhQgonLy67w6CUooU04WgdwG9k3xo", "User Schedules!B4:Z"), MATCH(INDIRECT(J5), 
 IMPORTRANGE("1LM7yIJJ_w6mftXJhQgonLy67w6CUooU04WgdwG9k3xo", "User Schedules!2:2"), 0)-1, 0), "NONE YET")))}

    enter image description here

    update:

    ={"SCHEDULE"; ARRAYFORMULA(IF(INDIRECT(J6)="",,IFNA(VLOOKUP(INDIRECT(J6), 
 IMPORTRANGE("1LM7yIJJ_w6mftXJhQgonLy67w6CUooU04WgdwG9k3xo", B3&"!B4:1000"), MATCH(INDIRECT(J5), 
 IMPORTRANGE("1LM7yIJJ_w6mftXJhQgonLy67w6CUooU04WgdwG9k3xo", B3&"!2:2"), 0)-1, 0), "NONE YET")))}

    0