Let's say I have a set of marks as follows

NAME | SCORE |
---|---|

AVA | 13 |

BERTIE | 46 |

GRAHAM | 87 |

And a table of grade boundaries as follows:

GRADE | BOUNDARY |
---|---|

A | 88 |

B | 45 |

C | 9 |

How can I easily make a formula in Excel such that it...

- Looks at the mark someone gets
- Checks if it is with (say) 2 marks of any of the grade boundaries
- Returns a YES if the score is within that range else a NO
- Returns how far above or below the relevant boundary they are.

So for the above, desired result is:

NAME | SCORE | BORDERLINE? |
---|---|---|

AVA | 13 | NO |

BERTIE | 46 | YES +1 |

GRAHAM | 87 | YES -1 |

I've lost myself in nested if statements and in VLOOKUP and can't see the wood for the trees...