multidimensional index and match function to condense table

Hi,I have very large data that I want to condense. I just can not figureout how to do this. I have tried many if,match and vlookup function but no luck.

This is the kind of database I have (I am actually working with monthly inventory usage but I just made this pseudo example). Here S=Student (A,B,C and D). T1,T2,T3,T4=tests

S T1 T2 T3 T4
A 50 50
B
C 40
D 30


What I want to do it,
If in new sheet I write T1 and T4, following is the output I like to see.
S T1 T4
A 50
B 30

Can someone help me how to do this? I also like this table to be updated if I add another Test in first row.

Without a excel sheet I should say use a combination of 'index' and 'match' function

popipipo has the right idea

The first cell in your new table would have:

Formula:

Please Login or Register  to view this content.

Then just copy it across and down as needed.


Formula Logic for future use:

Index(search where,what row, what column)
Match(match what, where,[0 for exact matches])

Index(test scores,Match(student,all the students,0),Match(test#,all the test #s,0))

The absolute references "$" in the equation allow you to copy and paste the equation around while keeping the references in their place.

This pulls a test score in the row matching the students name and a column matching the given test#.