|
Vlookup function
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 "Pete Cumberland" wrote in message ... 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 |
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 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Pete Cumberland" wrote in message ... 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 |
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 "Pete Cumberland" wrote in message ... 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 |
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) -- Dana DeLouis Win XP & Office 2003 "Pete Cumberland" wrote in message ... 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 |
Can the 35,45, etc be referenced in cells F1, F2, etc???
Jim "CLR" wrote in message ... 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 "Pete Cumberland" wrote in message ... 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 |
=LOOKUP(A1,sheet1!E1:F5)
or with a RangeName, =LOOKUP(A1,Grades) Vaya con Dios, Chuck, CABGx3 "Jim May" wrote in message news:5GEoe.65417$sy6.64809@lakeread04... Can the 35,45, etc be referenced in cells F1, F2, etc??? Jim "CLR" wrote in message ... 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 "Pete Cumberland" wrote in message ... 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 |
Thanks for answering,
your suggestion works fine. Jim "CLR" wrote in message ... =LOOKUP(A1,sheet1!E1:F5) or with a RangeName, =LOOKUP(A1,Grades) Vaya con Dios, Chuck, CABGx3 "Jim May" wrote in message news:5GEoe.65417$sy6.64809@lakeread04... Can the 35,45, etc be referenced in cells F1, F2, etc??? Jim "CLR" wrote in message ... 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 "Pete Cumberland" wrote in message ... 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 |
You're more than welcome............thanks for the feedback........
Vaya con Dios, Chuck, CABGx3 "Jim May" wrote in message news:qLKoe.66135$sy6.38608@lakeread04... Thanks for answering, your suggestion works fine. Jim "CLR" wrote in message ... =LOOKUP(A1,sheet1!E1:F5) or with a RangeName, =LOOKUP(A1,Grades) Vaya con Dios, Chuck, CABGx3 "Jim May" wrote in message news:5GEoe.65417$sy6.64809@lakeread04... Can the 35,45, etc be referenced in cells F1, F2, etc??? Jim "CLR" wrote in message ... 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 "Pete Cumberland" wrote in message ... 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 |
| All times are GMT +1. The time now is 10:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com