How do I reference a cell as PART of a vlookup "Table_Array" locat

Basically, I have a matrix, and the top axis is the same as the name of other
pages in my workbook. The left axis are components that are on multiple pages
(the pages named in the top axis). I want to do a vlookup to populate the
matrix, and I need to reference the top axis as part of the vlookup formula:

=VLOOKUP(A4, (CONCATENATE("'", B1, "'!$A$3:$B$78")), 2,FALSE)

Thus, for worksheet named by B1, for array A3:B78, lookup the value in A4,
and return the value in the B column of the above array.

The trouble occurs when I try to reference the other worksheet via the value
in B1. Proper syntax from the "ADDRESS" function is '101'!$A$3:$B$78, which
my CONCATENATE function returns when run separately. Also, I have tried the
"INDIRECT" function surrounding the "CONCATENATE" function with the same
#VALUE error.

Anyone know a work-around or better way to achieve this?

=vlookup(a4,indirect("'" & b1 & "'!A3:b78"),2,false)

I used the & operator instead of the =concatenate function, but that would work,
too.

-Rocket wrote:

Basically, I have a matrix, and the top axis is the same as the name of other
pages in my workbook. The left axis are components that are on multiple pages
(the pages named in the top axis). I want to do a vlookup to populate the
matrix, and I need to reference the top axis as part of the vlookup formula:

=VLOOKUP(A4, (CONCATENATE("'", B1, "'!$A$3:$B$78")), 2,FALSE)

Thus, for worksheet named by B1, for array A3:B78, lookup the value in A4,
and return the value in the B column of the above array.

The trouble occurs when I try to reference the other worksheet via the value
in B1. Proper syntax from the "ADDRESS" function is '101'!$A$3:$B$78, which
my CONCATENATE function returns when run separately. Also, I have tried the
"INDIRECT" function surrounding the "CONCATENATE" function with the same
#VALUE error.

Anyone know a work-around or better way to achieve this?

--

Dave Peterson

Works perfectly. Many thanks Dave!

"Dave Peterson" wrote:

=vlookup(a4,indirect("'" & b1 & "'!A3:b78"),2,false)

I used the & operator instead of the =concatenate function, but that would work,
too.

-Rocket wrote:

Basically, I have a matrix, and the top axis is the same as the name of other
pages in my workbook. The left axis are components that are on multiple pages
(the pages named in the top axis). I want to do a vlookup to populate the
matrix, and I need to reference the top axis as part of the vlookup formula:

=VLOOKUP(A4, (CONCATENATE("'", B1, "'!$A$3:$B$78")), 2,FALSE)

Thus, for worksheet named by B1, for array A3:B78, lookup the value in A4,
and return the value in the B column of the above array.

The trouble occurs when I try to reference the other worksheet via the value
in B1. Proper syntax from the "ADDRESS" function is '101'!$A$3:$B$78, which
my CONCATENATE function returns when run separately. Also, I have tried the
"INDIRECT" function surrounding the "CONCATENATE" function with the same
#VALUE error.

Anyone know a work-around or better way to achieve this?

--

Dave Peterson