Vlookup using different sheets

C

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

T. Valko

Try something like this:

A1 = H29 (a sheet name)

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

Cpt. Costanzo

T. Valko said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top