vlookup with dynamic table_array

[Jim Toohey]

I'm trying to use vlookup with multiple worksheets defined by a dynamic
table_array value. I can store the appropriate value in a cell with the
correct format, but the vlookup function returns a REF error.

Example:

=VLOOKUP(DataCollection!B8,'Beds101-200'!A1:I68,7) works fine if I type the
specific table_array name in.

If I build the table_array name in cell o1, like this
=TRIM("'"&TRIM(N1)&"'!B1:I68"), which displays like this:
'Beds101-200'!A1:I68, I can't get the function to work.

Here is the final cell format =vlookup(DataCollection!B8,$o$1,7)
I've also tried =vlookup(DataCollection!B8,"$o$1",7)

Is there a way to get this to work?

 

[Biff]

Try it like this:

N1 = Beds101-200 (or whatever sheet name: Beds201-300)

=VLOOKUP(DataCollection!B8,INDIRECT("'"&N1&"'!A1:I68"),7)

Biff

 

[Jim Toohey]

Biff:
Worked like a charm. Thanks.
--
Jim T

Try it like this:

N1 = Beds101-200 (or whatever sheet name: Beds201-300)

=VLOOKUP(DataCollection!B8,INDIRECT("'"&N1&"'!A1:I68"),7)

Biff

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Biff:
Worked like a charm. Thanks.