Lookup values in a two way table

[Guest]

Hi - I have a table containing exam codes in column A, then the percentage
needed to obtain each grade in that exam e.g.

Code A B C D
1423 80% 60% 40% 30%
1424 20% 60% 50% 40%
1425 75% 65% 55% 45%

I then have another list of exam codes and the percentage a student achieved:

Exam code % Grade
1423 0.7

I need to be able to look up the exam code in the top array, and find the
relevant grade boundary for that exam then return the grade they got in the
Grade box. I think I could do it if I got excel to return the row number
containing the Exam Code but I'm not sure -all help much appreciated!!

Thanks

 

[Earl Kiosterud]

Snara,

If you arrange your table like this, starting in A1:

Code D C B A
1423 30% 40% 60% 80%
1424 40% 50% 60% 80%
1425 45% 55% 65% 75%

You had a 20% for grade A in the row for grade A, which I've assumed is a typo and have
changed to 80%.

Use this:
=INDEX(B1:E1,1,MATCH(70%,OFFSET(B1,MATCH(1423,A2:A4,0),0,1,4),1))

Substitute your cell references for the 70% and the 1423 in the formula.