VLOOKUP with a twist

[Mark]

I have been usng VLOOKUP to convert test marks to grades for a while now.
However we are now required to tell the students how close they are - or how
lucky they are - in the grade.

There are three possible scenarios:

1 They are in the midle of the band: Just a grade.
2 They have just hit the pass mark for that band: Grade followed by "-"
3 They are one mark (for example) away from the next grade boundary: Grade
followed by "+"

I can do this by hand, but that seems to be defeating the object of using XL.
I could overly complicate the Lookup table, again seemingly a waste.

I know it's probably basic, but I can't seem to sort this out!!

Any help greatfully received

 

[J.E. McGimpsey]

One way:

Assume the following lookup table on Sheet2:

A B
1 Score Grade
2 0 F
3 60 D
4 70 C
5 80 B
6 90 A
7 1000

(where A7 is some arbitrarily large number.

In sheet1, assume the score is in J1. Then

K1: =VLOOKUP(J1,Sheet2!A:B,2,TRUE) &
IF((J1-INDEX(Sheet2!A:A,MATCH(J1,Sheet2!A:A,TRUE)))<=1,"-","") &
IF((INDEX(Sheet2!A:A,MATCH(J1,Sheet2!A:A)+1)-J1)<=1, "+","")

You can adjust the ranges which get + and - by changing <=1 to some
other comparison value.

As written, you'll get the following results:

J1 K1
58 F
59 F+
60 D-
61 D-
62 D
68 D
69 D+
70 C-
... ...
99 A
100 A

 

[Mark]

One way:

Wow! Tahnks - I'll file it away and have a better look at the weekend!