Vlookup look up the remark

[htkhim2990]

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

 

[Ashish Mathur]

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

 

[Bob Bridges]

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 there: =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?)