I am using Excel 2019

I have a formula =VLOOKUP(A2,'B1'!D:D,1,TRUE)

First Argument A2 (Name of client) Second Argument Range to search for this client's name, which is in another sheet on the same workbook named "01 Nov 2021" which is in named in cell B1 Third Argument is just 1 as the range to search is only 1 col Forth argument FALSE as I want an exact match

I'm getting an error whereby the formula would not read the value of B1 and takes B1 as the sheet name which does not exist, and therefore produces an error.

Question: How can I change the formula such that the formula would read the value of B1 instead of taking B1 itself for the 2nd argument such that the formula would function.

Refer to image for a visual understanding of what I'm having trouble with.

I am aware that I can simply replace "B1" with "01 Nov 2021" and the formula would function, but I want this formula to be dynamic in the sense where if I change the value in cell B1 to maybe another date, it will read and search it accordingly.

CodePudding user response：

Solution Found: =VLOOKUP(A2,INDIRECT("'"&B1&"'!D:D"),1,FALSE)

by using Indirect() I'm able to reference the cell without error