The students in my school are not all scheduled to attend every day.

In Google Sheets I have a sheet, 'student list' that lists the students that attend classes for each given weekday:

  1. The first row contains the weekdays, Monday through Friday.

  2. In each column, starting from row 2, the names of the students are listed whose schedules include that weekday

  3. Sample Table

    A B C D E
    1 Monday Tuesday Wednesday Thursday Friday
    2 Sally Ron Rick Jasper Unil
    3 Ron John Sally Ron John
    4 John Sally Pedro Louise Vespa

In another sheet named 'attendance list' I have a dropdown that contains the names of the weekdays in A1.

What formula can I use to return the student names from 'student list' based on the weekday selected in the dropdown?

Through my research I have been able to use the ARRAYFORMULA function to return a specific range of cells from the same document, but I can't figure out how to combine that with an IF function based on the dropdown.

Link to my sample spreadsheet

  • 1
    Welcome to Web Applications. The question needs to be narrower for this site. You should narrow its scope to something more specific. You should describe your spreadsheet structure, include sample data, and show the expected result. Jan 19 at 4:44
  • Congratulations! You have reached a reputation score of 20. You might have received some notifications about the privileges that you have earned in your Stack Exchange Inbox. I want to put the spotlight on Web Applications Chat (it requires a have earned 20 reputation points). In the early days of Stack Exchange, the chat was called the "Third Space," where the rules were a lot simpler; in simple words, basically talking about stuff related to the site topics, such as web applications and being nice. Jan 19 at 16:52
  • By the way, the main chatroom for Web Applications is Google-Fu. Jan 19 at 17:52

1 Answer 1


Formula in 'attendance list'!A2

   INDEX('student list'!A2:E,,
         MATCH(A1, 'student list'!A1:E1,0)), 1)

If you prefer to have the results returned as a row, replace the TOCOL function by TOROW:

   INDEX('student list'!A2:E,,
         MATCH(A1, 'student list'!A1:E1,0)), 1)

Function Support


  • 1
    here docs.google.com/spreadsheets/d/… is the link with a second table outline. I want to have Monday which is separated into three columns to be carried over to the table to populate with names. I tried but it only brings the column.
    – Steven
    Jan 23 at 4:50
  • 1
    Thanks again for replying. I tried typing out the TOROW formula and when filling in the different parts the INDEX asks for [reference] which is the 'student list 2' table but the [row] and [column] are the same when you wrote the TOCOL and TOROW code. why would they not be swapped? Please check the link if my explanation for what I tried is unintelligible.
    – Steven
    Jan 25 at 4:54
  • @Steven Sorry, I misunderstood the new constraint. Please create a new question and I and others would be happy to help with it. You can reference this question in the new one for context.
    – Blindspots
    Jan 25 at 13:43
  • @Steven in the new question please indicate details such as whether the source data will always have 3 columns per weekday, and for the expected result, whether it should be in 1 column or split across columns
    – Blindspots
    Jan 25 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.