Vlookup using different sheets

[Cpt. Costanzo]

I wonder if anyone can work this one out for me!
I want the same Vlookup but applied to different sheets. We have lots of
different classes sitting the same exam. Each class has a different sheet. We
average the mark for each question. I want to compare the averages for each
question, for each class.
I use VLookup to get the average by looking up average in the sheet and then
returing the questions scores, (=VLOOKUP($B$3,'H29'!$A:$H,D$2,FALSE))
So $B$3 is the word "Average" which appears in the array A:$H (In this
instance I want the question score at col index $D$2).
My question is how, without retyping can I substitute different classes, on
different sheets (here 'H29') to appear in the Vlookup? It doesnt work if you
just reference a cell with the correct class name, or using the ampasand. Be
really pleased for an answer as I've puzzled this for ages!

 

[T. Valko]

Try something like this:

A1 = H29 (a sheet name)

=VLOOKUP($B$3,INDIRECT("'"&A1&"'!A:H"),D$2,0)

 

[Cpt. Costanzo]

Try something like this:

A1 = H29 (a sheet name)

=VLOOKUP($B$3,INDIRECT("'"&A1&"'!A:H"),D$2,0)

Thanks that's perfect. Im going to have a bit of think about my new discovery INDIRECT!! Not quite sure I completely get it but it's certainly done the trick. Cheers

 

[T. Valko]

You're welcome. Thanks for the feedback!