Vlookup function

[Pete Cumberland]

I want to use vlookup to convert grades (A, B, C, etc) to numeric values but
have been unable to do so. I've been to the Contextures web page which
gives help on this and have read, in confusion, a solution but am still
unable to get it to work.

This is what I've done:
Sheet 1 I've called "Numeric"

and I've entered a series of text grades in column A

I've created a table of grades and numbers in sheet "Lookup" which is
entered in cells A1:B6 with cells A2:B6 being the field array
Grade Score
E 35
D 45
C 55
B 65
A 75


In Sheet "Numeric" column B adjacent to the text grades (A1:A5) I've
entered the formulas

=VLOOKUP(A1,Lookup!A2:B6,2)
=VLOOKUP(A2,Lookup!A2:B6,2)
=VLOOKUP(A3,Lookup!A2:B6,2)
=VLOOKUP(A4,Lookup!A2:B6,2)
=VLOOKUP(A5,Lookup!A2:B6,2)

which, bizarrely, gives the result:

D 75.00
C 75.00
A 75.00
B 65.00
E 75.00


Why is this happening?

Pete

 

[KL]

Hi Pete,

You are almost there. Y have just forgotten the fourth argument which tells
VLOOKUP wheather the lookup range is sorted alfabetically/ascendingly or
not. In your case you need to search for exact matches in a non sorted list
so your formula should have the 4th argument FALSE or simply 0. When the
list is sorted you can either ommit the fourth argument or use TRUE or 1.
Also, since you are copying your formula down I recommend that you use an
absolute reference for your source table, like this:

=VLOOKUP(A1,Lookup!$A$2:$B$6,2,0)

Regards,
KL

 

[Max]

In sheet: Numeric
--------------
Put in B1: =VLOOKUP(A1,Lookup!$A$2:$B$6,2,0)
Copy B1 down to B5

You'll get the results:

D 45
C 55
A 75
B 65
E 35

2 observations
a. You have to fix/lock the table_array with dollar signs viz.
use: Lookup!$A$2:$B$6
b. The 4th param in VLOOKUP must be set to "0" or FALSE for an exact match
in this instance

 

[CLR]

Another way is to use just the LOOKUP feature.......

=LOOKUP(A1,{"A","B","C","D","E";35,45,55,65,75})

Vaya con Dios,
Chuck, CABGx3

 

[Dana DeLouis]

As another option, if you use data | validation to make sure you only enter
A-E, then perhaps two other formulas could be something like this.
With a letter like "A" in cell A1:

=MOD(835, CODE(A1) + 11)
or
=725-10*CODE(A1)

 

[Jim May]

Can the 35,45, etc be referenced in cells F1, F2, etc???
Jim

 

[CLR]

=LOOKUP(A1,sheet1!E1:F5)

or with a RangeName,

=LOOKUP(A1,Grades)


Vaya con Dios,
Chuck, CABGx3

 

[Jim May]

Thanks for answering,
your suggestion works fine.
Jim

 

[CLR]

You're more than welcome............thanks for the feedback........

Vaya con Dios,
Chuck, CABGx3

Thanks for answering,
your suggestion works fine.
Jim

=LOOKUP(A1,sheet1!E1:F5)

or with a RangeName,

=LOOKUP(A1,Grades)


Vaya con Dios,
Chuck, CABGx3