LOOKUP sequence not correctly matching

[GregoriM]

Hi

I am trying to create a template to calculate a GPA based on Module Grades and Grade Point Values. Whether I specify the array by cells or define them in an array within the formula the sequence is not applied correctly. Excel does not like the '+' and '-' symbols, in this case A+, A, A- given the value 4.2, 4, 3.8. The formula shows 3.8 for A+ instead of 4.2 and other mismatches occur down the line. Now, if I make it A1, A2, A3 it works but this is not the input I have to work with.

Any ideas on managing to use the +- symbols?

File attached...

Thanks
Gregori

[sandy666]

try: =CHOOSE(MATCH(B2,{"a+","a","a-","b+","b","b-","c+","c","c-","d+","d","d-","e","f","g","PC","PR","ng"},0),4.2,4,3.8,3.6,3.4,3.2,3,2.8,2.6,2.4,2.2,2,1.6,1,0.4,1.6,1.6,0)

[Pete_UK]

The list of items needs to be sorted (by ASCII codes, not by how we see the items), so you need to change your formula in C2 to this:

=LOOKUP(B2,{"A",4;"A-",3.8;"A+",4.2;"B",3.4;"B-",3.2;"B+",3.6;"C",2.8;"C-",2.6;"C+",3;"D",2.2;"D-",2;"D+",2.4;"E",1.6;"F",1;"G",0.4;"NG",0;"PC",1.6;"PR",1.6})

Hope this helps.

Pete

[Phuocam]

Sort A+,A,A- from A to Z

=LOOKUP(B2,{"a";"a-";"a+";"b";"b-";"b+";"c";"c-";"c+";"d";"d-";"d+";"e";"f";"g";"ng";"PC";"PR"},{4;3.8;4.2;3.4;3.2;3.6;2.8;2.6;3;2.2;2;2.4;1.6;1;0.4;0;1.6;1.6})

[GregoriM]

Thanks everyone, all solutions appear to work.

[sandy666]

If the basic problem is solved
- it's always a good practice to show respect to the person(s) who have helped you
- is a click on the Add Reputation first (left lower corner of the post of person(s) who helped you) and then
- mark the thread as SOLVED (top right corner over your first post - Thread Tools). This is important for all of us,
Thanks

[sandy666]

Thanks for feedback and of course try

btw. Sorting is not required