Please find the reference Google Sheet here.
(View only access, please File > Make A Copy)
I am trying to take a data table for a work conference schedule of sessions (“Session Details” sheet) and have it automatically populate a vertical agenda format (“Master Agenda” sheet).
On the “End Goal” sheet, you can see what I am ultimately trying to accomplish.
I have currently set up the following formula in C16 to pull data from the “Session Details” sheet to populate the Session Name into the “Master Agenda” sheet:
=IFNA(INDEX('Session Details'!$E:$E,MATCH(1,('Session Details'!$A:$A=$C$2)*('Session Details'!$B:$B=$A16)*('Session Details'!$D:$D=C$3),0)))
Which is working well, as it finds the 10:00 AM Marketing Session occurring on Monday, September 9 and returns the Session Name:
What I can’t figure out how to do (if possible), is to then pull in the remaining relevant data for that session (i.e. Start Time, End Time, Presenters, Location) to also populate the cell.
Ideally, I would want it to look like it does on the “End Goal” sheet (see below) and span the entire length of the session (from 10:00 AM to 11:00 AM):
Any way to utilize the INDEX MATCH formula I have currently to pull in other relevant data from the same row and ideally display it in a readable format (merged cell or not)?
4
Here’s one formulaic approach which you may adapt accordingly (does not give your idealistic expected output of merged style thing though; hopefully someone will contribute via a script):
=map(A4:A69,lambda(Ξ,map(C3:E3,lambda(Γ,let(Λ,'Session Details'!A2:index('Session Details'!H:H,match(,0/('Session Details'!A:A<>""))),Σ,lambda(x,choosecols(Λ,x)),
ifna(filter(Σ(5)&char(10)&to_text(Σ(2))&" - "&to_text(Σ(3))&char(10)&Σ(6)&char(10)&Σ(8), Σ(2)=Ξ, Σ(4)=Γ, Σ(1)=C2)))))))
1
Here’s a quick draft to get the details – works only in Excel (Microsoft 365). I will try to get Google Sheets version later. Getting merged cells via formula is not possible to my knowledge (as teylyn also commented).
Using variables is my preferred way, hopefully you don’t mind it. Compact code is not my goal here 🙂
This is an (dynamic) array formula, entered in a single cell, C4 in the example:
=LET(
session_details, 'Session Details'!$A$2:$H$8,
format_detail, LAMBDA(detail_row,
LET(
start_time, INDEX(session_details, detail_row, 2),
end_time, INDEX(session_details, detail_row, 3),
session_name, INDEX(session_details, detail_row, 5),
presenter, INDEX(session_details, detail_row, 6),
location, INDEX(session_details, detail_row, 8),
newline, CHAR(10),
time_format, "hh:mm AM/PM",
CONCAT(
session_name,
newline,
TEXT(start_time, time_format),
" - ",
TEXT(end_time, time_format),
newline,
presenter,
newline,
location
)
)
),
b_units, C3:E3,
start_times, A4:A69,
session_date, C2,
date, INDEX(session_details, , 1),
start_time, INDEX(session_details, , 2),
b_unit, INDEX(session_details, , 4),
arr, LAMBDA(n, CHOOSE(n, start_times, b_units)),
details, MAP(
arr({1}),
arr({2}),
LAMBDA(session_start, session_bu,
LET(
detail_row, MATCH(
1,
(date = session_date) * (start_time = session_start) * (b_unit = session_bu),
0
),
IF(ISNA(detail_row), "", format_detail(detail_row))
)
)
),
details
)
(I’m using arr lambda by DjC to map with two arrays in this manner – instead of MAKEARRAY.)
Result
Google Sheets version
using MAKEARRAY and ARRAYFORMULA:
=LET(
session_details, 'Session Details'!$A$2:$H$8,
session_date, C2,
date, INDEX(session_details, , 1),
start_time, INDEX(session_details, , 2),
b_unit, INDEX(session_details, , 4),
format_detail, LAMBDA(detail_row,
LET(
start_time, INDEX(session_details, detail_row, 2),
end_time, INDEX(session_details, detail_row, 3),
session_name, INDEX(session_details, detail_row, 5),
presenter, INDEX(session_details, detail_row, 6),
location, INDEX(session_details, detail_row, 8),
newline, CHAR(10),
time_format, "hh:mm AM/PM",
CONCATENATE(
session_name,
newline,
TEXT(start_time, time_format),
" - ",
TEXT(end_time, time_format),
newline,
presenter,
newline,
location
)
)
),
b_units, C3:E3,
start_times, A4:A69,
arr, LAMBDA(n, CHOOSE(n, start_times, b_units)),
details, arrayformula(MAKEARRAY(
ROWS(start_times),
COLUMNS(b_units),
LAMBDA(r, c,
LET(
session_start, INDEX(start_times, r, 1),
session_bu, INDEX(b_units, 1, c),
detail_row, MATCH(1, (date = session_date) * (start_time = session_start) * (b_unit = session_bu), 0),
IF(ISNA(detail_row), "", format_detail(detail_row))
)
)
)),
details
)
Result:
3