vlookup - can a formula be used to generate the table_array parameter?

VickiKP

New Member
Joined
Apr 1, 2015
Messages
2
I have a number of worksheets which contain audit score results (they are all the same format), there is one for each individual, therefore I have labelled the worksheets with their names. On a summary worksheet, I want to display the same field in each of the worksheets.

E.g. In Jon's sheet there is:

A B
1 Test Name Test Result
2 Test 1 1
3 Test 2 1
4 Test 3 3


E.g. In Bob's sheet there is:

A B
1 Test Name Test Result
2 Test 1 2
3 Test 2 3
4 Test 3 3


In the summary sheet, I want to see:

A B C
1 Jon Bob
2 Test 1 1 2
3 Test 2 1 3
4 Test 3 3 3

So I have a lookup in cell B2 , which is =VLOOKUP(A2,Jon!$A:$B,2,false). I want to be able to lookup the name so that I don't have to create a separate formula for all the names (I have 70), but I can't find a way to use a formula which the lookup will use as a valid table array.

Please help!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Welcome to the board.

You can use Indirect For that
Say B1 contains the sheet name (Jon)

=VLOOKUP(A2,INDIRECT("'"&B$1&"'!$A:$B"),2,false)
 
Upvote 0
Hi,

Put your worksheet name in c1 and you can use this. Change the name in c1 for another worksheet.


=VLOOKUP(A2,INDIRECT("'" & C1 & "'!$A:$B"),2,FALSE)
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top