Get a value in the first row of table (header) after 2 lookups 1

[joaopazevedo]

Good night

How to get a value in the first row (header) of a data table, after doing 2 lookups with formulas?

http://imageshack.us/photo/my-image/94/explanationquestionothe.jpg/

The table is a matrix of: test scores in the columns header and age in the rows labels; the data are the results of the test.
The first lookup is to get the row that has the data for the age of the student.
The second lookup is get the cell that has the result of the test of the student.
The result is to get value of the 1st cell in the column that intersects the 2ª lookup cell, where is the score of the test.

I tried but couldn't figure it out. An answer in VBA is also welcomed even thought I think i could do that myself.

Thank you for your help!

 

[Gavona]

Use 2 named ranges.
[red]Rating[/red] for your column headings
[red]Ages[/red] for your list of ages

Assuming that your input cella are also named ranges "Age" and "score" I built up the formula as follows (colour represents the formula in the preceding instruction):

The Age row is found by =MATCH(Age,Ages,0)
The Rating column by =MATCH(Score,OFFSET(Rating,[red]MATCH(Age,Ages,0)[/red],0,1,),1)
The Rating itself by =INDEX(Rating,1,[blue]=MATCH(Score,OFFSET(Rating,[red]MATCH(Age,Ages,0)[/red],0,1,),1)[/blue])

Gavin

 

[joaopazevedo]

Thank you a lot for your quick answer!

It works.
I only found and error that was due to row counting that I couldn't figure out why, but subtracting one row did the job.

I'm really appreciated