First of all,
CONCATENATE(
thing1,
thing2,
thing3)
can be abbreviated
thing1 &
thing2 &
thing3
They mean the same, but the second form (obviously) takes less room, so some people might find it easier to read.
Your question seems to be a little vague –– you didn’t say when you want to reference the same workbook, and when you want access a different one. So fill in the blank. And since you seem to have the VLOOKUP
part working, the question comes down to the referencing part. In other words, how to adapt
INDIRECT(CONCATENATE("'[Datafile.xlsx]", B1, "'!$A1:F500"))
which we can abbreviate to
INDIRECT("'[Datafile.xlsx]" & B1 & "'!$A1:F500")
So try
INDIRECT("'" & IF(
condition, "", "[Datafile.xlsx]") & B1 & "'!$A1:F500")
where condition is the condition under which you want to use the current file. In words, concatenate a single quote ('
), conditionally the workbook name (in brackets) or not, then the sheet name (I assume that’s what’s in B1
), a second quote, an exclamation mark, and the cell range.