INDEX & MATCH functions with VLOOKUP function as {array} argument (Excel 2013, Windows 8.1)

[Colo_Mike]

(Excel 2013 / Windows 8.0)

Hello, I’m having trouble using the INDEX and MATCH functions with a variable “array” argument. The variable is a list of worksheet names (all of which are in this same workbook). This list of worksheet names is on another tab within the same workbook. My hope is a solution using functions. I have not learned to use VBA so, if possible, please limit your suggestions to use of functions. Thanks.

For example, the worksheet names I need to use in the INDEX and MATCH functions are listed on a separate worksheet named “MAIN” like this:

Worksheet “MAIN”:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column:[/TD]
[TD]B[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]Rows:[/TD]
[TD]"ID"[/TD]
[TD]"Worksheet Name"[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]100[/TD]
[TD]100_GCs[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]200[/TD]
[TD]200_Permits[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]300[/TD]
[TD]300_Demo[/TD]
[/TR]
[TR]
[TD]Etc...[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Then, on another worksheet (called "Bid Details"), I’m trying to use VLOOKUP to insert the appropriate worksheet name within the INDEX and MATCH functions. Here is a sample of the data on worksheet "Bid Details":

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column:[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Rows:[/TD]
[TD]"Code"[/TD]
[TD]"Description"[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]100.010[/TD]
[TD]**INDEX function goes here**[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]100.020[/TD]
[TD]**INDEX function goes here**[/TD]
[/TR]
[TR]
[TD]Etc...[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


On worksheet "Bid Details” I have a couple hundred rows, each using an INDEX/MATCH functions. In this example the worksheet name ("100_GCs") is hardcoded:
=INDEX('100_GCs'!$P$12:$Q$40,MATCH(RIGHT(B4,3),'100_GCs'!$Q$12:$Q$40),1)

This version of the function works, however I don’t want to have to hardcode the sheet name into each of my INDEX functions. Additionally, the range within this INDEX function (!$P$12:$Q$40) and the MATCH function ($Q$12:$Q$40) is the same regardless of which worksheet I’m referring to (“100_GCs”, “200_Permits”, etc.), which is why I’m only using VLOOKUP to grab the worksheet name.

Here is an example of the VLOOKUP function I’m using to grab the worksheet names:
=VLOOKUP($B$2,MAIN!B8:J56,9,FALSE)

Where:
* B2 is cell B2 on the “Bid Details” worksheet containing the ID per the table above (100, 200, etc.)
* “MAIN” is the worksheet name where these ID’s are associated with the worksheet names per the table above

And here’s the function (that isn’t working) with the VLOOKUP inserted in place of the hardcoded worksheet name:
=INDEX(VLOOKUP($B$2,MAIN!B8:J56,9,FALSE)!$P$12:$Q$40,MATCH(RIGHT(B4,3), VLOOKUP($B$2,MAIN!B8:J56,9,FALSE)!$Q$12:$Q$40),1)

I’ve tried this with numerous uses of single and double quotes around the VLOOKUP functions with no luck – I get the typical, generic popup window saying I have a problem with my function.

Thanks in advance for any suggestions!

 

[KyleUK]

Would Indirect work?

=INDEX(INDIRECT(VLOOKUP($B$2,MAIN!B8:J56,9,FALSE) & "!$P$12:$Q$40"),MATCH(RIGHT(B4,3), INDIRECT(VLOOKUP($B$2,MAIN!B8:J56,9,FALSE) & "!$Q$12:$Q$40")),1)

 

[Colo_Mike]

Thanks, Kyle. I did try some versions of INDIRECT however I don't think I entered it as you have it shown. I'll try that and follow up this afternoon.

 

[Colo_Mike]

Yes! That worked! I just learned about INDIRECT last week so was obviously not using it correctly in this scenario. Thanks for the help, Kyle. Have a great day.