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? |
How do I reference a cell as PART of a vlookup "Table_Array" locat
=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 |
How do I reference a cell as PART of a vlookup "Table_Array" l
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 |
All times are GMT +1. The time now is 11:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com