# Check if result is near a borderline in excel

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:

A 88
B 45
C 9

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

1. Looks at the mark someone gets
2. Checks if it is with (say) 2 marks of any of the grade boundaries
3. Returns a YES if the score is within that range else a NO
4. 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...

• The easiest way is to expand your table of grade boundaries, adding rows with all valid values and a column with ready-made “Yes+1”. In this case, you won't have to build a very complex formula; MATCH() will be enough to find the desired row and INDEX() to get the value. And if MATCH() does not find a score in the table, then IFERROR() will return “NO”. Commented Oct 4, 2023 at 14:36

``````=TRIM(SUBSTITUTE(CONCAT(IF(ABS(C17-\$H\$17:\$H\$19)<=2,"YES " & TEXT(C17-\$H\$17:\$H\$19,"+0;-0"), " ")),"   ","NO"))