Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup look up the remark
Had been trying to use Vlookup function to look up the
comments in the given table below , but it doesn't work well. Appreciate if anyone can enlighten me what went wrong with the formula? The formula issued : =Vlookup(C6,G6:H12,2) There after I copied the formula to the rest of table, and end result as shown in the table. What was wrong with the formula? Thank you col A B C D Row Name Marks Grade Comments 6 Chin Ai Looi 45 E Perfect Score 7 Fandi Ahmad 55 D Perfect Score 8 Peter Brown 76 B Perfect Score 9 Tracy Ong 84 B Perfect Score 10 Jason Bond 86 A High Distinction 11 Linday Tay 90 A High Distinction 12 Ravi Gopal 92 A #N/A 13 kendra Ong 100 A+ #N/A Row Marks Grade Comments 7 0 E Fail 8 50 D Pass 9 65 C Credit 10 75 B Distinction 11 85 A High Distinction 12 100 A+ Perfect Score 1) Use the VLOOKUP function lookup the grade and Comments from the given table |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup look up the remark
Hi,
Not sure if you are trying to lookup one value or multiple values. If you are indeed trying to look up one value, then put 0 as the last argument of the VLOOKUP() function. If you are looking up for more than I value, then you need to use the SUMPRODUCT() function. -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com wrote in message ... Had been trying to use Vlookup function to look up the comments in the given table below , but it doesn't work well. Appreciate if anyone can enlighten me what went wrong with the formula? The formula issued : =Vlookup(C6,G6:H12,2) There after I copied the formula to the rest of table, and end result as shown in the table. What was wrong with the formula? Thank you col A B C D Row Name Marks Grade Comments 6 Chin Ai Looi 45 E Perfect Score 7 Fandi Ahmad 55 D Perfect Score 8 Peter Brown 76 B Perfect Score 9 Tracy Ong 84 B Perfect Score 10 Jason Bond 86 A High Distinction 11 Linday Tay 90 A High Distinction 12 Ravi Gopal 92 A #N/A 13 kendra Ong 100 A+ #N/A Row Marks Grade Comments 7 0 E Fail 8 50 D Pass 9 65 C Credit 10 75 B Distinction 11 85 A High Distinction 12 100 A+ Perfect Score 1) Use the VLOOKUP function lookup the grade and Comments from the given table |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup look up the remark
You didn't say what columns your grade table is in, but if the marks are in
G, the grades in H and the comments in I then I would make the following changes: 1) You're using the marks to find the grade and comment, right? So the first argument must be from col B, not col C: =VLOOKUP(B6,G6:H12,2) 2) I would search starting in G7, not G6. G6 contains the column headers, and since you're telling Excel that all the rows in the lookup table are in ascending order...well, I don't know whether Excel would say "Marks" comes before or after 45, but anyway it makes sense if the table must be sorted to have it search only in the part of the data that you want it to find a match in: =VLOOKUP(B6,G$7:H$12,2) 3) For the grade, this formula should work fine. But the comments are in the next column over, so you have to expand the table by one column: =VLOOKUP(B6,G$7:I$12,3) In order to make it as easy as possible, I'd put the same table argument in both columns, that is, =VLOOKUP(B6,$G$7:$I$12,2) in C and =VLOOKUP(B6,$G$7:$I$12,3) in D. In fact, what I'd really do is put the table on a separate worksheet, and then call it the =VLOOKUP(B6,Grades!$A$1:$C$6,2). But that's up to you; it isn't necessary, and as long as you don't need to add or delete student rows it isn't even beneficial. (But how likely is it that you won't need to add or delete or sort student rows?) --- " wrote: Had been trying to use Vlookup function to look up the comments in the given table below , but it doesn't work well. Appreciate if anyone can enlighten me what went wrong with the formula? The formula issued : =VLOOKUP(C6,G6:H12,2) There after I copied the formula to the rest of table, and end result as shown in the table. What was wrong with the formula? col A B C D Row Name Marks Grade Comments 6 Chin Ai Looi 45 E Perfect Score 7 Fandi Ahmad 55 D Perfect Score 8 Peter Brown 76 B Perfect Score 9 Tracy Ong 84 B Perfect Score 10 Jason Bond 86 A High Distinction 11 Linday Tay 90 A High Distinction 12 Ravi Gopal 92 A #N/A 13 kendra Ong 100 A+ #N/A Row Marks Grade Comments 7 0 E Fail 8 50 D Pass 9 65 C Credit 10 75 B Distinction 11 85 A High Distinction 12 100 A+ Perfect Score |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste text into remark | Excel Discussion (Misc queries) | |||
Excel: if in advance unknow# repeated in row get remark in a cell | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Cos help remark should say "RADIANS" rather than "COS". | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |