If formula not working.....

[Losguapos1]

Good morning,

I have attached a sample data sheet. in column H the points needed column, it isn't calculating some of my students data. I ova the formula set to look up what grade they are in and then check the score range and once it does that it tells me how many point they need to reach the next level. If they are level 4 it tells me by how many points.

All my 6th graders are working fine but my 7th and 8th graders are giving me errors and i cannot figure out why.

Any and all help would be greatly appreciated!!!!

TIA

Los

[63falcondude]

Try this in H4:

=IF(E4="","",E4-IFERROR(INDEX('Score Ranges'!B$2:E$4,MATCH(D4,'Score Ranges'!A$2:A$4,0),G4+1),LOOKUP(D4,'Score Ranges'!A$2:A$4,'Score Ranges'!E$2:E$4)))

[Jonmo1]

I don't know why your formula errors on 7 an 8. I'm not going to try to figure it out.
There's a far simpler way to accomplish the task using index and match.

Try this in Sheet1 H4 and filled down

=IF(E4="","",E4-INDEX('Score Ranges'!$C$2:$E$4,MATCH(D4,'Score Ranges'!$A$2:$A$4),MIN(3,G4)))

[Losguapos1]

thank you both!!! reps all around.

It is all fixed i went with Jonmo1's solution

[Jonmo1]

You're welcome.

[63falcondude]

Glad we could help. Thanks for the rep!

[mehmetcik]

Try this formula in G4

Please Login or Register  to view this content.

and this formula in H4

Please Login or Register  to view this content.