Search code examples
excelmultidimensional-arrayexcel-formulatimetable

How to populate a table by looking up columns in another table?

Screenshot of tables

I have a table (left) where I am defining, per row, the date/time of lessons for an individual.

I have a second table (right) which is my resulting timetable.

I am trying to use formula to populate the timetable based on the planning table - but without luck. I have tried multiple INDEX/MATCH formula plundered from web searches... but none achieve the correct result.

Each cell in the timetable, defined by their relative Time row value and Day column header values, should be populated with the corresponding Initials value where the Time/Day combo map to the Session/Day columns of the planning table.

Hence TUESDAY, 09:00-09:30 on the timetable should be calculated as AB, because cells K3 (Day) and L3 (Time) are mapped in the planning table - with the resulting Initials being AB (cell C3).

Hopefully the image will explain better than my words.

All suggestions gratefully received.

Links to sources I have tried:

Solution

  • Assuming no Excel Constraints as per the tags posted, then I believe this should do what you are looking for:

    enter image description here

    • Formula used in cell Q2

    =LET(
     a, SEQUENCE(,COLUMNS(Table1[[Session 1 - Day]:[Session 3 - Time]])/2),
     b, TOCOL(IFS(a,Table1[Initials])),
     c, WRAPROWS(TOCOL(Table1[[Session 1 - Day]:[Session 3 - Time]]),2),
     d, HSTACK(b,c),
     XLOOKUP(1,($P2=INDEX(d,,3))*(Q$1=INDEX(d,,2)),TAKE(d,,1),""))

    The above formula needs to fill down and fill right!. However, you can use the following one as well, which spills for the whole data.

    enter image description here

    • Formula used in cell Q2

    =LET(
     a, SEQUENCE(,COLUMNS(Table1[[Session 1 - Day]:[Session 3 - Time]])/2),
     b, TOCOL(IFS(a,Table1[Initials])),
     c, WRAPROWS(TOCOL(Table1[[Session 1 - Day]:[Session 3 - Time]]),2),
     d, HSTACK(b,c),
     MAKEARRAY(ROWS(P2:P13),COLUMNS(Q1:U1),LAMBDA(_r,_c,XLOOKUP(1,
     (INDEX(P2:P13,_r)=INDEX(d,,3))*(INDEX(Q1:U1,_c)=INDEX(d,,2)),TAKE(d,,1),""))))

    Note: From the screenshots of the post, looks like you are using Structured References aka Tables, therefore I have used tables in the formulas above and name of the table is Table1 makesure to change the name in the formula as per suit.

    Another way:

    enter image description here

    • Formula used in cell Q2

    =LET(
     a, _Int&"|"&_Day&"|"&_Time,
     b, IFS(ISERROR(LEFT(TEXTAFTER(a,"|"))/1),a),
     c, TEXTSPLIT(TEXTAFTER("|"&TOCOL(b,3),"|",{1,2,3}),"|"),
     MAKEARRAY(ROWS(P2:P13),COLUMNS(Q1:U1),LAMBDA(_r,_c,XLOOKUP(1,
     (INDEX(P2:P13,_r)=INDEX(c,,3))*(INDEX(Q1:U1,_c)=INDEX(c,,2)),TAKE(c,,1),""))))

    Where:

    _Day refers to =Table1[[Session 1 - Day]:[Session 3 - Time]]

    _Int refers to =Table1[Initials]

    _Time refers to =Table1[[Session 1 - Time]:[Session 3 - Time]]

    are defined named ranges which will automatically grab the structured references for a new entry in the records.

    enter image description here