# Odd problem with VLOOKUP or LOOKUP

#### DeezNuts

##### Board Regular
I am trying to convert letter grades to GPA. I have looked at all the sites on the first 2 pages of Google before I came here to make a fool of myself again.

If I use VLOOKUP with the lists in a certain order I get the wrong results for B+ says 2.7 which it should be 3.3 If I reorder the list and use the same formula the B+ will be 3.3 but the A no longer shows 4.0 instead it shows a N/A

I can always use

Code:
VLOOKUP(A23,{"A",4;"A-",3.7;"B+",3.3;"B",3;"B-",2.7;"C+",2.3;"C",2;"D",1;"F",0},2,FALSE)

Just curious what am doing wrong with both VLOOKUP or LOOKUP

Excel 2013
ABCDEFGH
1B+2.7B+2.7
2A4A4
3A-3.7A-3.7
4B+3.3B+3.3
5B3B3
6B-2.7B-2.7
7C+2.3C+2.3
8C2C2
9D1D1
10F0F0
11
12A#N/AA#N/A
13F0F0
14D1D1
15C2C2
16C+2.3C+2.3
17B-2.7B-2.7
18B3B3
19B+3.3B+3.3
20A-3.7A-3.7
21A4A4
22
23B+3.3

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
B1=LOOKUP(A1,C2:D10)
F1=VLOOKUP(E1,G2:H10,2)
B12=LOOKUP(A12,C13:D21)
F12=VLOOKUP(E12,G13:H21,2)
B23=VLOOKUP(A23,{"A",4;"A-",3.7;"B+",3.3;"B",3;"B-",2.7;"C+",2.3;"C",2;"D",1;"F",0},2,FALSE)

<tbody>
</tbody>

<tbody>
</tbody>

EDIT*

No sooner do I post this but I see the error of my ways I tried
Code:
=VLOOKUP(E1,G2:H10,2,FALSE)
and it works perfectly. No clue what the False does but it works

Last edited:

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### JoeMo

##### MrExcel MVP
VLOOKUP with the fourth argument omitted defaults that argument to TRUE. Under those conditions the lookup values in your table must be in ascending order. B,B-,B+ is ascending order. Since your lookup table is not in ascending order, the formula returns the wrong value. You can set the fourth argument to FALSE to require an exact match that does not require the lookup table to be in any particular order.

For example in F1 enter:
=VLOOKUP(E1,G2:H10,2,FALSE)

#### DeezNuts

##### Board Regular
Thank you for explaining the False part JoeMo wasnt grasping that part. So if it is truly ascending no need for the true statement its assumed by excel but if I want the perfect side by side I have to add the false if its not ascending.

Replies
0
Views
130
Replies
3
Views
209
Replies
11
Views
308
Replies
2
Views
114
Replies
3
Views
234

1,195,953
Messages
6,012,511
Members
441,703
Latest member
clivelincoln

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back