0

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'!
Improve this question

1 Answer 1

Reset to default
1

Try,

=VLOOKUP(B3,INDIRECT(TEXT(B3-3,"'mm/dd/yyyy'!")&"B3:W300"),12,0)
 'or
=VLOOKUP(B3,INDIRECT(TEXT(B3-3,"'mm/dd/yyyy'!\B\3\:\W\3\0\0")),12,0)
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .