Lookup

[RussChilds]

Hi All,

I'm using the below table for as a lookup. This works fine.

My question is I would like a column that tells the pupil how many points from the next grade? For example a pupils scores 1341 which equals a MPP grade. They are 38 points from a MMP grade. How do I do this in a formula?

0 F
1299 PPP
1339 MPP
1379 MMP
1419 MMM
1459 DMM
1499 DDM
1529 DDD
1559 DDD*
1589 DD*D*
1590 D*D*D*

Many thanks!
Russ

 

[Aladin Akyurek]

Try...

=INDEX($A$1:$A$11,MATCH(D1,$A$1:$A$11,1)+(LOOKUP(D1,$A$1:$A$11)<D1))-D1

where D1 = 1341.

 

[Marcelo Branco]

Maybe...

=INDEX($A$1:$A$11,MATCH(D1,$A$1:$A$11,1)+1)-D1
where D1 = 1341

M.

 

[Marcelo Branco]

More robust
=INDEX(A$1:A$11,MATCH(D1,A$1:A$11,1)+1*ISNA(MATCH(D1,A$1:A$11,0)))-D1
where D1 = 1341

M.

 

[Aladin Akyurek]

Try...

=INDEX($A$1:$A$11,MATCH(D1,$A$1:$A$11,1)+(LOOKUP(D1,$A$1:$A$11) < D1))-D1

where D1 = 1341.

Edited to show the < evaluation.

 

[Aladin Akyurek]

More robust
=INDEX(A$1:A$11,MATCH(D1,A$1:A$11,1)+1*ISNA(MATCH(D1,A$1:A$11,0)))-D1
where D1 = 1341

M.

LOOKUP should be a tad faster than ISNA/MATCH.

 

[RussChilds]

Thanks Aladin Akyurek & Marcelo Branco. Very helpful!!!!!