Vlookup function

  • Thread starter Thread starter Pete Cumberland
  • Start date Start date
P

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
 
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
 
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
 
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
 
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)
 
=LOOKUP(A1,sheet1!E1:F5)

or with a RangeName,

=LOOKUP(A1,Grades)


Vaya con Dios,
Chuck, CABGx3
 
You're more than welcome............thanks for the feedback........

Vaya con Dios,
Chuck, CABGx3




Jim May said:
Thanks for answering,
your suggestion works fine.
Jim


CLR said:
=LOOKUP(A1,sheet1!E1:F5)

or with a RangeName,

=LOOKUP(A1,Grades)


Vaya con Dios,
Chuck, CABGx3
Click to expand...
Click to expand...
 
Back
Top