Check if within multiple ranges check problem

[branston]

Hi


I am trying to check if a candidates score is within three marks of the UPPER BOUNDARY of list of numbers. If it is I just need a simple YES (or colour code the cell)


For eg

Grade boundaries
A -B -C -D -E
9 35 56 99 134


So if a candidates score was 54 then a YES should appear as the candidate is 2 marks away from a C Grade. However if the candidates scored 11 marks I do not need a YES as I am only interested in the Upper Boundary only ie. if the candidate is within 3 marks of the Higher Grade (than the ones they actually achieved).


I hope that makes sense and can anyone help? I've attached my test file.

[branston]

I am trying something along the lines of


=IF(B2<=I2+3,HLOOKUP(B2,F2:L3,2,TRUE),"No")

However this is not checking the whole range just a particular value +3 (ie. I2 + 3).

Am I along the right lines?

[branston]

(Can anyone help… I’ve tried various formations to no avail. I desperately need this for this weekend.)

[arnelgp]

maybe try this formula:

=IF(HLOOKUP(B2,$F$2:$L$2,1,TRUE)>=($J$2-3),"Yes","")

[branston]

Thanks arnelgp

Not sure how the fixed $J$2 reference works. The candidates scores vary so need to look at the whole array rather than comparing with just one value? I attached my test file in post #1 so you can see what I mean.

[georgiboy]

=IF(HLOOKUP(B2+3,$F$2:$L$3,2,TRUE)<>C2,"YES","")

[Aussiebear]

Whoa.... Georgiboy. that a good response .

[branston]

Thanks georgiboy - that's awesome!

[snb]

=INDEX($F$2:$L$2;MATCH($B2;$F$2:$L$2;1)+1)-4<$B2