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" <[email protected]> wrote in message
news:[email protected]...
> 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???
>
>
>
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
<[email protected]> 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
> <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
>> <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >> <[email protected]> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >> <[email protected]> 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
<[email protected]> 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > 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
>> > >> <[email protected]> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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???
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
> >

Please, see the Help function on VLOOKUP :

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
with
lookup_value = value to be found in the first column of the array
table_array = table of information in which data is looked up
col_index_num = column number in table_array from which the matching
value must be returned
range_lookup = logical value that specifies whether you want VLOOKUP
to find an exact match or an approximate match

Greetings,
Bruno



"Neoseraph" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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???
>
>
>
>
>
>