Error on formula - help!!

**JackNevin** · 01-05-2012, 06:57 AM

Hi
Im using microsoft excel for my a-level project.
my project is for a primary school teachers grading system.
However when i change the possible marks from 100 to anything above that the VLOOKUP formula will not work correctly and 1%, 3% 5% 7% ect will *** up with #N/A this is by changing the possible marks from 100 to 200.
The formula that i'm using is =VLOOKUP(H14,'Grades Table'!E5:F105,2,FALSE)

Is there anyway or anything that I will be able to do so no matter how many marks the tests are out of this error will not appear

Please see attachment for a copy of the workbook. if any ways to solve the problem please comment.!

**teylyn** · 01-05-2012, 07:07 AM

Please have a look at the forum rules and then come up with a more descriptive title for your problem.

thanks,

**Bmoe** · 01-05-2012, 07:08 AM

Try =IF(H14 > 1, "5a", VLOOKUP(H14,'Grades Table'!E5:F105,2,FALSE))

This will look at the grade and if its > 100% (1) it will give it a 5a (Or you can put whatever you want to denote > 100%), if it is less it will continue with the vlookup as normal.

There is also ifError(VLOOKUP(H14,'Grades Table'!E5:F105,2,FALSE), "5a") but can be dangerous when looking up something like this.

A 3d way would be changing the last argument of the vlookup to "TRUE" I think, it will take the closest value since you have a sorted list but you would need to resort it in the other direction. It has to be ascending.