HMATCH star matching

[Walter Briscoe]

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?

 

[Bob Phillips]

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]

In message <#[email protected]> of Tue, 28 Aug 2007
12:47:44 in microsoft.public.excel.worksheet.functions, Bob Phillips

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"})

I am sorry I failed to explain myself adequately.
That maps 0 to 16- onto "U", 16 to 22- onto "G", etc.

I want to do it the other way round:
A* maps to 58;
A maps to 52;
B maps to 46;
etc.

If I was to use LOOKUP, "H" would map to 0 and "BC" would map to 40,
etc. because LOOKUP matches a value in a range to a precise value.
I want to map one of a set of precise values to a precise value and
other values not to match.
e.g.
A* 58
AB #NA
A 52
B 46
BC #NA
C 40
....
G 16
H #NA
U 0
V #NA
etc.

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},

That should have been
52, 58, 46, 40, 34, 28, 22, 16, 0},

 

[Peo Sjoblom]

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)

 

[Walter Briscoe]

In message <[email protected]> of Tue, 28 Aug 2007
09:00:30 in microsoft.public.excel.worksheet.functions, Peo Sjoblom

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,2
2,16,0},2,0)

Thank you! Both work. I had started with LOOKUP which needs sorted input
and "A" < "A*".

 

[Harlan Grove]

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,"~","~~"),"*","~*"),"?","~?")

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))

 

[Walter Briscoe]

In message <[email protected]> of
Wed, 29 Aug 2007 06:43:40 in microsoft.public.excel.worksheet.functions,

...

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.