I'm trying to reference current sheet - 3 as the table_array in a Vlookup function. The problem is I don't have a cell with the name of the sheet to reference directly to. So instead I'm referencing a cell value (today's date) - 3. That is the name of the sheet I need to reference. I've added in a TEXT function to convert to mm/dd/yyyy format as that is the format the sheet name is in. The issue I'm having is the TEXT function is leaving an extra " in front of the date. I don't know if I've been staring at this too long but I cannot figure out why this is happening, please help.
=VLOOKUP(B3,INDIRECT("'"&TEXT((B3-3),"mm/dd/yyyy")&"'!"&"B3:W300"),12,0)
When I evaluate the formula, he TEXT function including concatenated "'" and "'!" become
"'01/07/2020'!"
This ends as
"'01/07/2020'!