Forum Discussion

MattP817's avatar
MattP817
Copper Contributor
Sep 06, 2023

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 sheet, production is going to the date and shift located at the top of the sheet, and then working down, they are entering the data points. 

I would like to have the formula lookup the date in C2(Titan T1 Display Sheet) and then the shift from D2 then pull the data point from that KPI from the data sheet. 

Here is my formula that I am getting a "#Value" error on:

=XLOOKUP($C$2,'Titanium Data'!3:3,XLOOKUP('Titan T1 Display Sheet'!$D$2,'Titanium Data'!4:4,XLOOKUP(C4,'Titanium Data'!$C$5:$C$45,'Titanium Data'!$D$5:$ARY$45)))

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.