|
VLOOKUP Function
Hi, I need help to setup the VLOOKUP function for an assignment. Everytime I
try to do it, it says, #NA. I need to use VLOOKUP to put in the letter grade for a list of students's grades in % on their midterms. The average % is in cells D8:D18. The lookup table I made (in cells I9:J13) is as followed: 85% and above A 70% and above B 65% and above C 50% and above D Below 50% F This is what I tried for VLOOKUP but got #N/A: lookup value: D8 table array: I9;J13 col_index_num: 2 so the final formula looks like this: =VLOOKUP(D8,I9:J13,2) and that gave me #N/A. Can anybody help please??? |
VLOOKUP assumes your table is in ascending order, not descending. Change
your table to: 0.00 F 0.50 D 0.65 C 0.70 B 0.85 A And it should work. -- Regards, Fred Please reply to newsgroup, not e-mail "Neoseraph" wrote in message ... Hi, I need help to setup the VLOOKUP function for an assignment. Everytime I try to do it, it says, #NA. I need to use VLOOKUP to put in the letter grade for a list of students's grades in % on their midterms. The average % is in cells D8:D18. The lookup table I made (in cells I9:J13) is as followed: 85% and above A 70% and above B 65% and above C 50% and above D Below 50% F This is what I tried for VLOOKUP but got #N/A: lookup value: D8 table array: I9;J13 col_index_num: 2 so the final formula looks like this: =VLOOKUP(D8,I9:J13,2) and that gave me #N/A. Can anybody help please??? |
Hmmm, yeah, now that gave me "A" for grades that are higher then 0.85 but
then when I try to drag the formula down into the other cells, they all gave me "A" too even when the grade is 5% (which is suppose to be "F"), so what's wrong? "Fred Smith" wrote: VLOOKUP assumes your table is in ascending order, not descending. Change your table to: 0.00 F 0.50 D 0.65 C 0.70 B 0.85 A And it should work. -- Regards, Fred Please reply to newsgroup, not e-mail "Neoseraph" wrote in message ... Hi, I need help to setup the VLOOKUP function for an assignment. Everytime I try to do it, it says, #NA. I need to use VLOOKUP to put in the letter grade for a list of students's grades in % on their midterms. The average % is in cells D8:D18. The lookup table I made (in cells I9:J13) is as followed: 85% and above A 70% and above B 65% and above C 50% and above D Below 50% F This is what I tried for VLOOKUP but got #N/A: lookup value: D8 table array: I9;J13 col_index_num: 2 so the final formula looks like this: =VLOOKUP(D8,I9:J13,2) and that gave me #N/A. Can anybody help please??? |
How do I setup the True or False as range lookup?
"Bruno Vermeersch" wrote: You omitted True or False as Range_Lookup. This means that True is assumed. So : your values must be in ascending order. Try this in the cells I9:J13 0% F 50% D 65% C 70% B 85% A It should be working now, Bruno "Neoseraph" wrote in message ... Hi, I need help to setup the VLOOKUP function for an assignment. Everytime I try to do it, it says, #NA. I need to use VLOOKUP to put in the letter grade for a list of students's grades in % on their midterms. The average % is in cells D8:D18. The lookup table I made (in cells I9:J13) is as followed: 85% and above A 70% and above B 65% and above C 50% and above D Below 50% F This is what I tried for VLOOKUP but got #N/A: lookup value: D8 table array: I9;J13 col_index_num: 2 so the final formula looks like this: =VLOOKUP(D8,I9:J13,2) and that gave me #N/A. Can anybody help please??? |
Ok, here's what I did for the Range_lookup part:
I put in 0.00 and it give me false instead of true if I put 0.85, but then that gave me #NA again. So what's wrong? "Neoseraph" wrote: How do I setup the True or False as range lookup? "Bruno Vermeersch" wrote: You omitted True or False as Range_Lookup. This means that True is assumed. So : your values must be in ascending order. Try this in the cells I9:J13 0% F 50% D 65% C 70% B 85% A It should be working now, Bruno "Neoseraph" wrote in message ... Hi, I need help to setup the VLOOKUP function for an assignment. Everytime I try to do it, it says, #NA. I need to use VLOOKUP to put in the letter grade for a list of students's grades in % on their midterms. The average % is in cells D8:D18. The lookup table I made (in cells I9:J13) is as followed: 85% and above A 70% and above B 65% and above C 50% and above D Below 50% F This is what I tried for VLOOKUP but got #N/A: lookup value: D8 table array: I9;J13 col_index_num: 2 so the final formula looks like this: =VLOOKUP(D8,I9:J13,2) and that gave me #N/A. Can anybody help please??? |
Another method of grading as you desire.
Enter this formula in B1. You don't need a separate table, the table is in the formula. =LOOKUP(A1,{0,50,65,70,85},{"F","D","C","B","A"}) Enter a number in A1 Gord Dibben Excel MVP On Sun, 3 Apr 2005 08:15:03 -0700, Neoseraph wrote: Hi, I need help to setup the VLOOKUP function for an assignment. Everytime I try to do it, it says, #NA. I need to use VLOOKUP to put in the letter grade for a list of students's grades in % on their midterms. The average % is in cells D8:D18. The lookup table I made (in cells I9:J13) is as followed: 85% and above A 70% and above B 65% and above C 50% and above D Below 50% F This is what I tried for VLOOKUP but got #N/A: lookup value: D8 table array: I9;J13 col_index_num: 2 so the final formula looks like this: =VLOOKUP(D8,I9:J13,2) and that gave me #N/A. Can anybody help please??? |
Ok, when I tried your method, it gives me #REF! either by entering a number
in A1 or the cell A1 itself, please help me solve the problem... "Gord Dibben" wrote: Another method of grading as you desire. Enter this formula in B1. You don't need a separate table, the table is in the formula. =LOOKUP(A1,{0,50,65,70,85},{"F","D","C","B","A"}) Enter a number in A1 Gord Dibben Excel MVP On Sun, 3 Apr 2005 08:15:03 -0700, Neoseraph wrote: Hi, I need help to setup the VLOOKUP function for an assignment. Everytime I try to do it, it says, #NA. I need to use VLOOKUP to put in the letter grade for a list of students's grades in % on their midterms. The average % is in cells D8:D18. The lookup table I made (in cells I9:J13) is as followed: 85% and above A 70% and above B 65% and above C 50% and above D Below 50% F This is what I tried for VLOOKUP but got #N/A: lookup value: D8 table array: I9;J13 col_index_num: 2 so the final formula looks like this: =VLOOKUP(D8,I9:J13,2) and that gave me #N/A. Can anybody help please??? |
If you get a REF error with that formula you must have either deleted
something linked to A1 or used a non existent reference. Try the formula in a new workbook and put in a value in A1 -- Regards, Peo Sjoblom "Neoseraph" wrote in message ... Ok, when I tried your method, it gives me #REF! either by entering a number in A1 or the cell A1 itself, please help me solve the problem... "Gord Dibben" wrote: Another method of grading as you desire. Enter this formula in B1. You don't need a separate table, the table is in the formula. =LOOKUP(A1,{0,50,65,70,85},{"F","D","C","B","A"}) Enter a number in A1 Gord Dibben Excel MVP On Sun, 3 Apr 2005 08:15:03 -0700, Neoseraph wrote: Hi, I need help to setup the VLOOKUP function for an assignment. Everytime I try to do it, it says, #NA. I need to use VLOOKUP to put in the letter grade for a list of students's grades in % on their midterms. The average % is in cells D8:D18. The lookup table I made (in cells I9:J13) is as followed: 85% and above A 70% and above B 65% and above C 50% and above D Below 50% F This is what I tried for VLOOKUP but got #N/A: lookup value: D8 table array: I9;J13 col_index_num: 2 so the final formula looks like this: =VLOOKUP(D8,I9:J13,2) and that gave me #N/A. Can anybody help please??? |
Sorry, I tried to open a new workbook and it still doesn't change anything,
this is what the formula looks like with a cell: =VLOOKUP(D8,{0,"F";0.5,"D";0.65,"C";0.7,"B";0.85," A"},2) and this is what the formula looks like with a new value: =VLOOKUP(45,{0,"F";0.5,"D";0.65,"C";0.7,"B";0.85," A"},2,0.85) "Peo Sjoblom" wrote: If you get a REF error with that formula you must have either deleted something linked to A1 or used a non existent reference. Try the formula in a new workbook and put in a value in A1 -- Regards, Peo Sjoblom "Neoseraph" wrote in message ... Ok, when I tried your method, it gives me #REF! either by entering a number in A1 or the cell A1 itself, please help me solve the problem... "Gord Dibben" wrote: Another method of grading as you desire. Enter this formula in B1. You don't need a separate table, the table is in the formula. =LOOKUP(A1,{0,50,65,70,85},{"F","D","C","B","A"}) Enter a number in A1 Gord Dibben Excel MVP On Sun, 3 Apr 2005 08:15:03 -0700, Neoseraph wrote: Hi, I need help to setup the VLOOKUP function for an assignment. Everytime I try to do it, it says, #NA. I need to use VLOOKUP to put in the letter grade for a list of students's grades in % on their midterms. The average % is in cells D8:D18. The lookup table I made (in cells I9:J13) is as followed: 85% and above A 70% and above B 65% and above C 50% and above D Below 50% F This is what I tried for VLOOKUP but got #N/A: lookup value: D8 table array: I9;J13 col_index_num: 2 so the final formula looks like this: =VLOOKUP(D8,I9:J13,2) and that gave me #N/A. Can anybody help please??? |
"Neoseraph" wrote: Sorry, I tried to open a new workbook and it still doesn't change anything, this is what the formula looks like with a cell: =VLOOKUP(D8,{0.00,0.50,0.65,0.70,0.85},{"F","D","C ","B","A"}) and this is what the formula looks like with a new value: =VLOOKUP(45,{0.00,0.50,0.65,0.70,0.85},{"F","D","C ","B","A"}) "Peo Sjoblom" wrote: If you get a REF error with that formula you must have either deleted something linked to A1 or used a non existent reference. Try the formula in a new workbook and put in a value in A1 -- Regards, Peo Sjoblom "Neoseraph" wrote in message ... Ok, when I tried your method, it gives me #REF! either by entering a number in A1 or the cell A1 itself, please help me solve the problem... "Gord Dibben" wrote: Another method of grading as you desire. Enter this formula in B1. You don't need a separate table, the table is in the formula. =LOOKUP(A1,{0,50,65,70,85},{"F","D","C","B","A"}) Enter a number in A1 Gord Dibben Excel MVP On Sun, 3 Apr 2005 08:15:03 -0700, Neoseraph wrote: Hi, I need help to setup the VLOOKUP function for an assignment. Everytime I try to do it, it says, #NA. I need to use VLOOKUP to put in the letter grade for a list of students's grades in % on their midterms. The average % is in cells D8:D18. The lookup table I made (in cells I9:J13) is as followed: 85% and above A 70% and above B 65% and above C 50% and above D Below 50% F This is what I tried for VLOOKUP but got #N/A: lookup value: D8 table array: I9;J13 col_index_num: 2 so the final formula looks like this: =VLOOKUP(D8,I9:J13,2) and that gave me #N/A. Can anybody help please??? |
Is D8 formatted to Percentage?
Entering 45 if not formatted to % will always return A because 45 is much higher than .85 Gord Dibben Excel MVP On Sun, 3 Apr 2005 11:07:02 -0700, Neoseraph wrote: "Neoseraph" wrote: Sorry, I tried to open a new workbook and it still doesn't change anything, this is what the formula looks like with a cell: =VLOOKUP(D8,{0.00,0.50,0.65,0.70,0.85},{"F","D","C ","B","A"}) and this is what the formula looks like with a new value: =VLOOKUP(45,{0.00,0.50,0.65,0.70,0.85},{"F","D","C ","B","A"}) "Peo Sjoblom" wrote: If you get a REF error with that formula you must have either deleted something linked to A1 or used a non existent reference. Try the formula in a new workbook and put in a value in A1 -- Regards, Peo Sjoblom "Neoseraph" wrote in message ... Ok, when I tried your method, it gives me #REF! either by entering a number in A1 or the cell A1 itself, please help me solve the problem... "Gord Dibben" wrote: Another method of grading as you desire. Enter this formula in B1. You don't need a separate table, the table is in the formula. =LOOKUP(A1,{0,50,65,70,85},{"F","D","C","B","A"}) Enter a number in A1 Gord Dibben Excel MVP On Sun, 3 Apr 2005 08:15:03 -0700, Neoseraph wrote: Hi, I need help to setup the VLOOKUP function for an assignment. Everytime I try to do it, it says, #NA. I need to use VLOOKUP to put in the letter grade for a list of students's grades in % on their midterms. The average % is in cells D8:D18. The lookup table I made (in cells I9:J13) is as followed: 85% and above A 70% and above B 65% and above C 50% and above D Below 50% F This is what I tried for VLOOKUP but got #N/A: lookup value: D8 table array: I9;J13 col_index_num: 2 so the final formula looks like this: =VLOOKUP(D8,I9:J13,2) and that gave me #N/A. Can anybody help please??? |
Make your 'table-array' references absolute. Instead of
=VLOOKUP(D8,I9:J13,2), use =VLOOKUP(D8,$I$9:$J$13,2). Then when you drag the formula, you'll still be reference the proper lookup table. "Neoseraph" wrote: Hmmm, yeah, now that gave me "A" for grades that are higher then 0.85 but then when I try to drag the formula down into the other cells, they all gave me "A" too even when the grade is 5% (which is suppose to be "F"), so what's wrong? "Fred Smith" wrote: VLOOKUP assumes your table is in ascending order, not descending. Change your table to: 0.00 F 0.50 D 0.65 C 0.70 B 0.85 A And it should work. -- Regards, Fred Please reply to newsgroup, not e-mail "Neoseraph" wrote in message ... Hi, I need help to setup the VLOOKUP function for an assignment. Everytime I try to do it, it says, #NA. I need to use VLOOKUP to put in the letter grade for a list of students's grades in % on their midterms. The average % is in cells D8:D18. The lookup table I made (in cells I9:J13) is as followed: 85% and above A 70% and above B 65% and above C 50% and above D Below 50% F This is what I tried for VLOOKUP but got #N/A: lookup value: D8 table array: I9;J13 col_index_num: 2 so the final formula looks like this: =VLOOKUP(D8,I9:J13,2) and that gave me #N/A. Can anybody help please??? |
Set your Grade Table up as follows
0 F 50% D 65% C 70% B 85% A Do Insert Name Define, and name this range GRADES Then, assuming your GradePercentage is in A1, use this formula to look up the grade =VLOOKUP(A1,GRADES,2,TRUE) Vaya con Dios, Chuck, CABGx3 "Neoseraph" wrote in message ... Hi, I need help to setup the VLOOKUP function for an assignment. Everytime I try to do it, it says, #NA. I need to use VLOOKUP to put in the letter grade for a list of students's grades in % on their midterms. The average % is in cells D8:D18. The lookup table I made (in cells I9:J13) is as followed: 85% and above A 70% and above B 65% and above C 50% and above D Below 50% F This is what I tried for VLOOKUP but got #N/A: lookup value: D8 table array: I9;J13 col_index_num: 2 so the final formula looks like this: =VLOOKUP(D8,I9:J13,2) and that gave me #N/A. Can anybody help please??? |
| All times are GMT +1. The time now is 07:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com