vlookup with dynamic table_array

J

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?
 
B

Biff

Try it like this:

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

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

Biff
 
J

Jim Toohey

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


Biff said:
Try it like this:

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

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

Biff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top