Multi-Table Lookup

Assuming tabs named for your tables as per below screenshot:- the formula in cell B3 of Table 3 is

=IFNA(INDEX(Table1!$B$2:$G$4,MATCH($A3,Table1!$A$2:$A$4,0),MATCH(B$1,INDEX(Table2!$B$2:$G$4,MATCH($A3,Table2!$A$2:$A$4,0),),0)),"")

which can then be copied to the neighbouring cells for which return values are required.

Assuming per OP, the data is using Structured References aka Tables (all the 3), and using MS365 Modern version of Excel, then using the references you can apply the formula in the output Table. One needs to copy the formula down and across:

=LET(
     PRN, Table3[@[Part Number]:[Part Number]],
     Headers, INDEX(Headers3, COLUMN(A1)),
     Key1, PRN & "" & DATEVALUE(Headers),
     Key2, PRN2 & "" & Body2,
     Key3, PRN1 & "" & Headers1,
     Output, IFERROR(VALUE(CONCAT(REPT(Body1, PRN & "" &
                   CONCAT(REPT(Headers2, Key2 = Key1))
                   = Key3))), ""),
     Output)

• All the ranges are converted to Structured References aka Tables and named as Table1, Table2, and Table3 respectively.

• Also, I have used defined names in order to make the references look shorter and easier to read (To define name, select range or cells, Goto Formulas Tab click on Define Name, give a new name and hit ok as the ranges will be auto selected).

  • PRN1 --> Refers to =Table1[Part Number]

  • PRN2 --> Refers to =Table2[Part Number]

  • Headers1 --> Refers to =Table1[[#Headers],[11-May]:[15-Jun]]

  • Headers2 --> Refers to =Table2[[#Headers],[11-May]:[15-Jun]]

  • Headers3 --> Refers to =Table3[[#Headers],[11-May]:[15-Jun]]

  • Body1 --> Refers to =Table1[[11-May]:[15-Jun]]

  • Body2 --> Refers to =Table2[[11-May]:[15-Jun]]

• Using the LET() function to define variables, even though they are redundant here, but to make it easier to read and debug, you can change the last variable Output to see what each variables does, as well as can use the Evaluate Feature found under the Formulas Tab to debug.

=LET(
     L, LAMBDA(a, [b], TOCOL(IFS(a  "", IF(ISOMITTED(b), a, b)), 3)),
     c, Body1,
     d, Body2,
     e, L(c, PRN1),
     f, DATEVALUE(L(c, Headers1)),
     g, L(d, PRN2),
     h, DATEVALUE(L(d, Headers2)),
     i, e & "" & f,
     j, XLOOKUP(i,
                 g & "" & L(d),
                 h, NA()),
     k, XLOOKUP(e & "" & j,
                 i,
                 L(c), NA()),
     P, IFERROR(PIVOTBY(e,
                         f,
                         k,
                         SINGLE, , 0, , 0), ""),
     P)

=IFNA(XLOOKUP(XLOOKUP(--Table3[[#Headers],[11-May]],
      XLOOKUP(Table3[@[Part Number]:[Part Number]],
              Table2[[Part Number]:[Part Number]], Table2),
              Table2[#Headers]), Table1[#Headers],
      XLOOKUP(Table3[@[Part Number]:[Part Number]],
              Table1[[Part Number]:[Part Number]], Table1)),"")

With real tables you can apply table names which provide the dynamic sizing of them.
Use the actual table names in the formula.
Drag and copy the formula in all the result cells from B16.

=LET(val,TOCOL(IF((Table6[#Data]=B$15)($A16=Table6[Part Number]),Table6[#Headers],1/0),2),
IFERROR(TOCOL(IF((Table7[Part Number]=$A16)(Table7[#Headers]=val),Table7[#Data],1/0),2),""))

The dates in Table6 data range and the result table must be the same type to get the correct result of the equal comparisons in the formula. i.e. Either string or date but the same.

Multi-Lookup in Excel Tables

=LET(
    pc,[@[Part Number]],
    rb,TEXT(CHOOSEROWS(Table2,XMATCH(pc,Table2[Part Number])),"@"),
    db,XLOOKUP(TEXT(@Table3[#Headers],"@"),rb,Table2[#Headers]),
    ra,CHOOSEROWS(Table1,XMATCH(pc,Table1[Part Number])),
    IFNA(XLOOKUP(db,Table1[#Headers],ra),"")
)

• After clearing Table3, copy the formula to its first row.

Hi MJM,

You can use below formula in cell R3 (see the screenshot).

=IFERROR(--TRANSPOSE(TEXTSPLIT(TEXTJOIN(";",, BYCOL(R2:W2, LAMBDA(b, ARRAYTOTEXT(IFERROR(BYROW(Q3:Q5, LAMBDA(a, TOCOL(1 / (1 / (B3:G5 / (A3:A5 = a)  (B2:G2 = TOCOL(J3:O5 / ((I3:I5 = a)  (J2:O2 = b)), 3)))), 3))), ""))))), ", ", ";")), "")

Or you can use this other solution.

=IFNA(--TEXTSPLIT(TEXTJOIN(";",, BYROW(Q3:Q5, LAMBDA(a, ARRAYTOTEXT(TOROW(BYCOL(R2:W2, LAMBDA(b, TOCOL(B3:G5 / ((I3:I5 = a) * (TEXT(J3:O5, "@") = TEXT(b, "@"))), 3))), 3))))), ", ", ";"), "")

Hope this helps.

IlirU