Xlookup with Textjoin or Concat using date and filename

[Joey_Ng]

Hi,
I have the below original formula for Xlookup which works fine.
Original Formula: =XLOOKUP(E3,'[8th August 2022 Report.xlsx]Sheet1'!$B:$B,'[8th August 2022 Report.xlsx]Sheet1'!$T:$T,,0))

Now, the date of the filename changed everyday, eg, 9th August then 10th August etc... therefore I manually enter the date in Cell T1 (so I don't have to change the formula everyday) and incorporate into the formula using CONCATENATE(T1," 2022 Report.xlsx"). I am hoping someone could assist me with the end formula. Something like below. I am confused as to whether or not to include '[ in the CONCATENTATE formula.

=XLOOKUP(E3,CONCATENATE($T$1," 2022 Report.xlsx"]Sheet1'!$B:$B,CONCATENATE($T$1," 2022 Report.xlsx"]Sheet1'!$T:$T,,0))

Thank You in advance.

Joey

 

[Fluff]

What exactly are you putting in T1?

 

[Joey_Ng]

What exactly are you putting in T1?

Hi Fluff,

T1 has the current date. For example, 8th August

Thanks,

Joey

 

[Fluff]

8th August is not a date, so do you have an actual date, or the text "8th August"?

 

[Joey_Ng]

Hi Fluff,

Sorry have been offline for a while...

Basically, I want to be able to look up someone else's file and everyday they amended the filename as the date in words, eg, 8th August plus 2022 Report. So the day after it is "9th August 2022 Report" and the day after that is "10th August 2022 Report". Instead of amending the "date in words" in the formula, I want to put the date in words (eg, 8th August) in a cell then use "Concat" or "textjoin" to join the date (8th August) with "2022 Report.xlsx" in a xlookup or vlookup formula (so the lookup filename is "8th August" & "2022 Report.xlsx"). Hope it make sense.

Thanks,

Joey

 

[Fluff]

Ok, how about

=XLOOKUP(E3,INDIRECT($T$1&" 2022 Report.xlsx]Sheet1'!$B:$B"),INDIRECT($T$1&" 2022 Report.xlsx]Sheet1'!$T:$T"),,0)

but the other workbook will need to be open.

 

[Joey_Ng]

Hi Fluff,

Thanks for that. I added ''[8th August in T1 to include '[ and it works well. Appreciate your help.

Thank You.

Joey

 

[Fluff]

Glad to help & thanks for the feedback.