Remember Me?

#1
November 13th 08, 07:01 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 51
VLOOKUP #N/A Error

Hi,

I'm using a VLOOKUP formula and I'm getting a #N/A
error! The calculation steps show error at the 'lookup_value'.I've tried by
best but cannot work through it.Can you please let me know how to get rid of
this.

Thanks!

#2
November 13th 08, 07:02 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 10,124
VLOOKUP #N/A Error

Look in the help index for ISNA
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"NM" wrote in message
...
Hi,

I'm using a VLOOKUP formula and I'm getting a #N/A
error! The calculation steps show error at the 'lookup_value'.I've tried
by
best but cannot work through it.Can you please let me know how to get rid
of
this.

Thanks!

#3
November 13th 08, 07:10 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Oct 2008 Posts: 1,805
VLOOKUP #N/A Error

Suppose you are using
=VLOOKUP(A1,C,2,False)

Then make sure that A1 is one of the values in Col C...

If your fourth parameter is TRUE or you are not using one then make sure
that Col C & D are sorted on Col C...

If you are using a formula in A1, then make sure it returns a valid value...

Just to test replace A1 by 10
and add one row with 10 in Col C and TEST in Col D
you should get TEST as a result of the formula above.

"NM" wrote:

Hi,

I'm using a VLOOKUP formula and I'm getting a #N/A
error! The calculation steps show error at the 'lookup_value'.I've tried by
best but cannot work through it.Can you please let me know how to get rid of
this.

Thanks!

#4
November 13th 08, 07:22 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2008 Posts: 79
VLOOKUP #N/A Error

would you post your formula and elaborate more what you want to do.
if your formula is correct, using ISNA with the Vlookup formula

"NM" wrote:

Hi,

I'm using a VLOOKUP formula and I'm getting a #N/A
error! The calculation steps show error at the 'lookup_value'.I've tried by
best but cannot work through it.Can you please let me know how to get rid of
this.

Thanks!

#5
November 13th 08, 08:26 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 51
VLOOKUP #N/A Error

Thanks Sheeloo! With your help I was able to fix my #N/A error.

Another question: What is the fastest way to calculate the column index
number.Say for eg. how do you calculate the column index number for columns
which fall to the extreme right? I mean my column index is BR, say my table
range starts from A, what will be the coulmn index number for BR? Do I count
all the way to BR?

"Sheeloo" wrote:

Suppose you are using
=VLOOKUP(A1,C,2,False)

Then make sure that A1 is one of the values in Col C...

If your fourth parameter is TRUE or you are not using one then make sure
that Col C & D are sorted on Col C...

If you are using a formula in A1, then make sure it returns a valid value...

Just to test replace A1 by 10
and add one row with 10 in Col C and TEST in Col D
you should get TEST as a result of the formula above.

"NM" wrote:

Hi,

I'm using a VLOOKUP formula and I'm getting a #N/A
error! The calculation steps show error at the 'lookup_value'.I've tried by
best but cannot work through it.Can you please let me know how to get rid of
this.

Thanks!

#6
November 13th 08, 08:41 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768
VLOOKUP #N/A Error

If you have unique column headers then you can use a MATCH function to get
the column_index_number for you.

...........A..........B..........C..........D
1....Name......H1........H2........H3
2....Sue..........x...........y...........z
3....Joe..........a...........b...........c

To lookup Joe and H2:

=VLOOKUP("Joe",A13,MATCH("H2",A11,0),0)

Result = b

--
Biff
Microsoft Excel MVP

"NM" wrote in message
...
Thanks Sheeloo! With your help I was able to fix my #N/A error.

Another question: What is the fastest way to calculate the column index
number.Say for eg. how do you calculate the column index number for
columns
which fall to the extreme right? I mean my column index is BR, say my
table
range starts from A, what will be the coulmn index number for BR? Do I
count
all the way to BR?

"Sheeloo" wrote:

Suppose you are using
=VLOOKUP(A1,C,2,False)

Then make sure that A1 is one of the values in Col C...

If your fourth parameter is TRUE or you are not using one then make sure
that Col C & D are sorted on Col C...

If you are using a formula in A1, then make sure it returns a valid
value...

Just to test replace A1 by 10
and add one row with 10 in Col C and TEST in Col D
you should get TEST as a result of the formula above.

"NM" wrote:

Hi,

I'm using a VLOOKUP formula and I'm getting a #N/A
error! The calculation steps show error at the 'lookup_value'.I've
tried by
best but cannot work through it.Can you please let me know how to get
rid of
this.

Thanks!

#7
November 13th 08, 10:01 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 51
VLOOKUP #N/A Error

I think my question was not clear enough. In VLOOKUP, when we enter column
index number, how do you quickly count the number for column BR. eg. the
table range is a1: BZ100. Then in VLOOKUP function what number do you put for
column BR in the column index number. Do I count all thy from A through BR?
A=1 ,B=2, C=3 ....BR=?

Let m eknow if I'm not clear.
Thanks!

"T. Valko" wrote:

If you have unique column headers then you can use a MATCH function to get
the column_index_number for you.

...........A..........B..........C..........D
1....Name......H1........H2........H3
2....Sue..........x...........y...........z
3....Joe..........a...........b...........c

To lookup Joe and H2:

=VLOOKUP("Joe",A13,MATCH("H2",A11,0),0)

Result = b

--
Biff
Microsoft Excel MVP

"NM" wrote in message
...
Thanks Sheeloo! With your help I was able to fix my #N/A error.

Another question: What is the fastest way to calculate the column index
number.Say for eg. how do you calculate the column index number for
columns
which fall to the extreme right? I mean my column index is BR, say my
table
range starts from A, what will be the coulmn index number for BR? Do I
count
all the way to BR?

"Sheeloo" wrote:

Suppose you are using
=VLOOKUP(A1,C,2,False)

Then make sure that A1 is one of the values in Col C...

If your fourth parameter is TRUE or you are not using one then make sure
that Col C & D are sorted on Col C...

If you are using a formula in A1, then make sure it returns a valid
value...

Just to test replace A1 by 10
and add one row with 10 in Col C and TEST in Col D
you should get TEST as a result of the formula above.

"NM" wrote:

Hi,

I'm using a VLOOKUP formula and I'm getting a #N/A
error! The calculation steps show error at the 'lookup_value'.I've
tried by
best but cannot work through it.Can you please let me know how to get
rid of
this.

Thanks!

#8
November 13th 08, 10:41 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768
VLOOKUP #N/A Error

I think my question was not clear enough.

No, it was clear! You're just not following what I tried to explain.

The column_index_number is *relative* to the lookup_table. If your table
range is A1:BZ100 then the column_index_number for column BR would be the
same as the column number for column BR. Column BR = 70 and this would also
be the column_index_number *realtive* to the lookup_table. So, you could use
this:

=VLOOKUP("Joe",A1:BZ100,COLUMN(BR1),0)

Which evaluates to:

=VLOOKUP("Joe",A1:BZ100,70,0)

However, if the table range was AA1:BZ100 then the column_index_number is no
longer the same as the column number for column BR (70). The
column_index_number for column BR would now be 44. As I tried to explain in
my other post, when this is the case then you can use the MATCH function to
*automatically* calculate the column_index_number for you instead of trying

--
Biff
Microsoft Excel MVP

"NM" wrote in message
...

I think my question was not clear enough. In VLOOKUP, when we enter column
index number, how do you quickly count the number for column BR. eg. the
table range is a1: BZ100. Then in VLOOKUP function what number do you put
for
column BR in the column index number. Do I count all thy from A through
BR?
A=1 ,B=2, C=3 ....BR=?

Let m eknow if I'm not clear.
Thanks!

"T. Valko" wrote:

If you have unique column headers then you can use a MATCH function to
get
the column_index_number for you.

...........A..........B..........C..........D
1....Name......H1........H2........H3
2....Sue..........x...........y...........z
3....Joe..........a...........b...........c

To lookup Joe and H2:

=VLOOKUP("Joe",A13,MATCH("H2",A11,0),0)

Result = b

--
Biff
Microsoft Excel MVP

"NM" wrote in message
...
Thanks Sheeloo! With your help I was able to fix my #N/A error.

Another question: What is the fastest way to calculate the column index
number.Say for eg. how do you calculate the column index number for
columns
which fall to the extreme right? I mean my column index is BR, say my
table
range starts from A, what will be the coulmn index number for BR? Do I
count
all the way to BR?

"Sheeloo" wrote:

Suppose you are using
=VLOOKUP(A1,C,2,False)

Then make sure that A1 is one of the values in Col C...

If your fourth parameter is TRUE or you are not using one then make
sure
that Col C & D are sorted on Col C...

If you are using a formula in A1, then make sure it returns a valid
value...

Just to test replace A1 by 10
and add one row with 10 in Col C and TEST in Col D
you should get TEST as a result of the formula above.

"NM" wrote:

Hi,

I'm using a VLOOKUP formula and I'm getting a #N/A
error! The calculation steps show error at the 'lookup_value'.I've
tried by
best but cannot work through it.Can you please let me know how to
get
rid of
this.

Thanks!

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post DeVoe Excel Worksheet Functions 3 April 11th 07 09:11 PM [email protected] Excel Discussion (Misc queries) 1 December 30th 06 10:44 PM PaulW Excel Discussion (Misc queries) 3 April 18th 06 01:32 PM Samantha Excel Worksheet Functions 1 April 11th 05 11:02 AM Josh O. Excel Worksheet Functions 6 December 30th 04 06:16 PM

All times are GMT +1. The time now is 08:29 AM.