How do you create an Xlookup with dynamic return array based on text in an input cell in Excel?

Question

I want to create a summary table in one workbook that uses Xlookup to match criteria in a table of data in another workbook. I would like the return array of the Xlookup to change based on the header of the table in the summary workbook.

I can successfully pull the data I need when specifying the named table and column like this.

=XLOOKUP(B9&B10,'2022 Farnam Performance Data Collector.xlsx'!ManagerKPI[[#All],[Entity Name]]&'2022 Farnam Performance Data Collector.xlsx'!ManagerKPI[[#All],[Metric Name]],'2022 Farnam Performance Data Collector.xlsx'!ManagerKPI[[#All],[1/21/2022]],"Not Found",,)

However, I want to be able to change the [1/21/2022]] reference based on the summary table header.

I tried an indirect reference and got #Ref. Checking calculation steps the #REF error occurs when pulling text from the cell I specified. Also if at all possible I would prefer to not have an indirect in my formula if at all possible.

=XLOOKUP(B9&B10,'2022 Farnam Performance Data Collector.xlsx'!ManagerKPI[[#All],[Entity Name]]&'2022 Farnam Performance Data Collector.xlsx'!ManagerKPI[[#All],[Metric Name]],INDIRECT("'2022 Farnam Performance Data Collector.xlsx'!ManagerKPI[[#All],["&1/21/2022&"]]", TRUE),"Not Found",,)

Answer 1

screenshots refer -

a) remove table formulae (file -> options -> formulas -> Use table names [deselect/uncheck box]

b) Include an index reference for the column lookup - SEE index component of following example that searches for 2022/1/21 (date formatted) in the SOURCE.xlsx file (bottom) to retrieve row1 to DEST.xlsx file (top):

=XLOOKUP(B9&B10,[SOURCE.xlsx]Sheet1!$B$2:$B$5,INDEX([SOURCE.xlsx]Sheet1!$C$2:$F$5,,MATCH(DATE(2022,1,21),[SOURCE.xlsx]Sheet1!$C$2:$F$2,0)),[SOURCE.xlsx]Sheet1!$C$2:$C$5,0)

voila!

---