I may have got the thresholds wrong so adjust if necessary

=LOOKUP(GRADE,{0,16,22,28,34,40,46,52},{"U","G","F ","E","C","B","A","A*","D"})

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Walter Briscoe" wrote in message
...
I am using Excel 2003 (11.6355.6360) SP1 on an XP machine.
I am attempting to write a worksheet function to translate a GCSE grade to
a value. (GCSE is General Certificate of Secondary Education )
The following grades and values are used
Grade A* A B C D E F G U
Value 58 52 46 40 34 28 22 16 0

Currently, I use a naive VBA function to do the job. (It consists of a
sequence of "if param = grade then func = value end if" clauses.)

On one line, I tried =HLOOKUP(GRADE,
{"A", "A*, "B", "C", "D", "E", "F", "G", "U";
52, 58, 52, 46, 40, 34, 28, 22, 16},
FALSE)

That works well for expected values other than "A*" (eh star) and for
unexpected values such as "What?" which returns #NA.
However, A GRADE of "A*" (eh star) matches "A" and 52 is returned.
A GRADE of "A~*" (eh tilde star) matches "A*" and 58 is returned.

Is this expected behaviour? (British spelling.)

If so, is there a function to literalize metacharacters such as "*"
(star)?

Is there any documentation covering the point?
--
Walter Briscoe