VLOOKUP formula inside Table_array

[scarlton]

Hello,

I am trying to have vlookup search another sheet for a certain value. The problem is I want it to search the sheets in the table_array based on one of the other cells. I want it to look at what value I have in b9, so the formula is =VLOOKUP(DATE(P5,1,1),B9!$A$3:$F$2520,2,TRUE) but when I press enter, it opens my documents on my computer instead of referencing the cell b9. Is there a formula that I need to put into the table array portion to make it work, or is it something else?

Thanks in advance

 

[inactiveuserps07]

=VLOOKUP(DATE(P5,1,1),INDIRECT(CHAR(39)&B9&“’!$A$3:$F$2520”),2,TRUE)

You have to use an INDIRECT cell reference, which is basically stringing together what you want the cell to look like. So this part of the above formula ...

INDIRECT(CHAR(39)&B9&“’!$A$3:$F$2520”)

... says, "Create a cell/range reference by stringing together a single quote plus whatever text is in cell B9 plus another single quote plus this cell range."

I could just as easily have done it this way:

INDIRECT("'"&B9&“’!$A$3:$F$2520”)

... but I think that placing a single quote between double quotes is hard to read; so I replaced the first instance with CHAR(39), which will return a single quote (character 39 on the ASCII table).

 

[Aladin Akyurek]

Concisely put like this:

=VLOOKUP(DATE(P5,1,1),INDIRECT("'"&B9&"'!A3:F2520"),2,TRUE)

Note that you have set the match-type to TRUE. This implies that A3:F2520 is sorted in ascending order on column A. If this set up does not hold, replace TRUE with FALSE or just with 0.

 

[scarlton]

Thank you guys so much!