Forum Discussion

MattP817's avatar
MattP817
Copper Contributor
Sep 06, 2023
Solved

Help with Xlookup syntax

Hello I am in need of some help. This is a production KPI/Tier board that I am setting up.  I need help setting up the general formula that I can continue on the rest of the sheet. On the data sh...
excel
Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Sep 06, 2023

    MattP817 

    Try this one:

    =LET(
    c, XMATCH(TEXT($C$2, "dddd, mmmm d, yyy"), Table3[#Headers]) + LEFT($D$2) - 1,
    return_array, CHOOSECOLS(Table3, c),
    XLOOKUP(C4, Table3[date], return_array, "")
)

     

    The 'dates' in the table are texts so I wrote the formula to convert the true date in C2 to a text string for the lookup.

Patrick2788's avatar
Patrick2788
Silver Contributor
Sep 06, 2023

MattP817 

Try this one:

=LET(
    c, XMATCH(TEXT($C$2, "dddd, mmmm d, yyy"), Table3[#Headers]) + LEFT($D$2) - 1,
    return_array, CHOOSECOLS(Table3, c),
    XLOOKUP(C4, Table3[date], return_array, "")
)

 

The 'dates' in the table are texts so I wrote the formula to convert the true date in C2 to a text string for the lookup.

Resources