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!
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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)
 

Mike LH

Well-known Member
Joined
Mar 17, 2015
Messages
566
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)
 

Forum statistics

Threads
1,086,245
Messages
5,388,671
Members
402,134
Latest member
McKnze21

Some videos you may like

This Week's Hot Topics

Top