Reference Cell In Same Row After Transposing in Google Sheets

Question

I have an example spreadsheet here where I have people who are scheduled for meetings at certain times as shown in sheet 1.

https://docs.google.com/spreadsheets/d/1IWt5VbaVxQoR6a5sDEj5eQYl4H9V85T-o5Q56dFEHyI/edit?usp=sharing

In sheet 2, I now want to log if each individual person can come to their meeting. So to do this, I used this formula to transpose and split the names into a list

=transpose(arrayformula(trim(split(join(",",Sheet1!B2:B6),","))))

This works but my issue is now how would I get the correct time into column b of sheet two for each person so that it references the correct time from sheet 1?

user_5632629 · Answer

try:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A, QUERY(SPLIT(FLATTEN(
 SPLIT(Sheet1!B2:B, ", ", )&"×"&Sheet1!A2:A), "×"), 
 "where Col2 is not null"), 2, )))

Reference Cell In Same Row After Transposing in Google Sheets

1 Answers