SOLVED

Help with Xlookup syntax

Copper Contributor

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)))

1 Reply
best response confirmed by MattP817 (Copper Contributor)
Solution

@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.

1 best response

Accepted Solutions
best response confirmed by MattP817 (Copper Contributor)
Solution

@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.

View solution in original post