In message .com of
Wed, 29 Aug 2007 06:43:40 in microsoft.public.excel.worksheet.functions,
Harlan Grove writes
"Peo Sjoblom" wrote...
Try either this

=VLOOKUP(Grade,{"A*",58;"A",52;"B",46;"C",40;"D" ,34;"E",28;
"F",22;"G",16;"U",0},2,0)

or this

=HLOOKUP(Grade,{"A*","A","B","C","D","E","F","G" ,"U";
58,52,46,40,34,28,22,16,0},2,0)
...

Picky: if Grade were A? both formulas would return 58; if Grade
were A~ both formulas would return 52; if Grade were just * both
formulas would return 58; if Grade were just ? both formulas would
return 52. If that doesn't matter to the OP, fine. However, if the OP
wants to kick out anything except the actual valid grades, then make
the 1st argument to the lookup functions

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Grade,"~","~~"), "*","~*"),"?","~?")

Thank you for being picky. I was so relieved that I could correctly
translate "A*" and "A" that I did not do full testing. I tried
=VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Grade,"~ ","~~"),"*","~*"),"?","~?"), ...

which works well apart from failure to distinguish "B" which is valid
and "b" which is not.


or use the array formula

=INDEX({58,52,46,40,34,28,22,16,0},
MATCH(TRUE,EXACT(Grade,{"A*","A","B","C","D","E", "F","G","U"}),0))


That returns the desired result
{58 ,52 ,#NA ,#NA} for
{"A*","A","A?","a"} and seems the best alternative to date.

Thank you very much.

I had missed the relevant documentation in the VLOOKUP help page.
--
Walter Briscoe