Using index match to find terms on multiple tables?

[tsiguy96]

Hi all! having an issue with this one. I have 6 years entered on a template and various school subjects under each. Id like a single graph where I can pull up a single subject (aka science) and compare the grades of various years against each other. any ideas? i think there is a single index match forum where we can highlight every year, then search teh year and the actual term under it, and then return the value under that, but i cant put it together. workbook attached, should be more clear. thanks everyone!

[Pete_UK]

Put this formula in A3 of Sheet3:

=IFERROR(INDEX(Sheet1!$A$3:$BD$3,MATCH(A2,Sheet1!$A$1:$BD$1,0)+MATCH($A1,Tests,0)-1),"")

Then you can copy it across into C3, E3 and G3. You will need to correct the year in Q1 of Sheet1.

Hope this helps.

Pete

[tsiguy96]

perfect! thank you!

[Pete_UK]

You're welcome - glad to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete