Combining 3 formulas

Hi All,

Sorry if im not the best at explaining this but here goes;

I am trying to show whether students are working above below or on target of grades.

The grades are in the format or 5c,5b,5a,6c,6b,6a etc.

To do this ive made 3 column with separate formulas, the first using a vlookup and table to change their predicted grade into a number, the second to change their current grade into a number. And the third is an IF to use the previous two columns to show "above", "below" or "on target".

Is there a way to do this in a single formula ? basically changing what ive done from three columns to one.

Hope my explanation helps. I attach the Spreadsheet just in case it was rubbish.

The below formula in AH2 and copied down.
Then there's no need for AF & AG.

Formula:

Please Login or Register  to view this content.

BSB

Try

=IFERROR(IF(VLOOKUP(I94:I307,$AO$9:$AP$21,2,FALSE)<VLOOKUP(C94:C307,$AO$9:$AP$21,2,FALSE),"Above",IF(VLOOKUP(I94:I307,$AO$9:$AP$21,2,FALSE)>VLOOKUP(C94:C307,$AO$9:$AP$21,2,FALSE),"Below","On Target")),"")

This worked perfectly thanks alot