Problem using LOOKUP (or MATCH) for Grades...

[aron]

Trying to use an Excel spreadsheet to convert letter grades to
numerical grades; i.e.;
A 95
A- 92
B+ 88
B 85
B- 82
C+ 78
C 75
C- 72
D 65
F 0
When using the LOOKUP function in Excel, it returns the following:
A 95
A- 92
B+ 82
B 92
B- 82
C+ 72
C 82
C- 72
D 65
F 0
Clearly, the LOOKUP (and MATCH) functions are being thrown off by the
presence of a "+" in a cell, as well as the cell following the cell
with the "+".
Here are the results of using the MATCH function:
A 1
A- 2
B+ 5
B 2
B- 5
C+ 8
C 5
C- 8
D 9
F 10
I would appreciate any help/explanations.

 

[Ken Johnson]

Trying to use an Excel spreadsheet to convert letter grades to
numerical grades; i.e.;
A 95
A- 92
B+ 88
B 85
B- 82
C+ 78
C 75
C- 72
D 65
F 0
When using the LOOKUP function in Excel, it returns the following:
A 95
A- 92
B+ 82
B 92
B- 82
C+ 72
C 82
C- 72
D 65
F 0
Clearly, the LOOKUP (and MATCH) functions are being thrown off by the
presence of a "+" in a cell, as well as the cell following the cell
with the "+".
Here are the results of using the MATCH function:
A 1
A- 2
B+ 5
B 2
B- 5
C+ 8
C 5
C- 8
D 9
F 10
I would appreciate any help/explanations.

With grades in column A, numerical values in column B and looked up
grade in column C...

=VLOOKUP(C1,$A$1:$B$10,2,FALSE)

The 3rd argument should equal FALSE so that the formula returns exact
values.

Ken Johnson

 

[aron]

Thank you. That works, but it is unclear why it is necessary, since
they were all exact matches...

 

[Geoff Lilley]

Ken is right. If you want EXACT matches, then you actually HAVE to
specify "FALSE" in the last argument of the VLOOKUP function, because
the default is "TRUE", or inexact match. As a result, 92, 93, and 94
would be A-, and anything above 95 would be an A, for example.

So , if you WANT the values to be inexact (i.e., between 92 and 95)
then TRUE is not required, because it is the default. However, if you
want an exact match, then, again, Ken is quite right to suggest the use
of FALSE.

HTH.

Cheers,
Geoff