Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
NM NM is offline
external usenet poster
 
Posts: 51
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,124
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,805
Default VLOOKUP #N/A Error

Suppose you are using
=VLOOKUP(A1,C:D,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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 79
Default 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
should solve your problem



"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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
NM NM is offline
external usenet poster
 
Posts: 51
Default 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?

Thanks for your help.

"Sheeloo" wrote:

Suppose you are using
=VLOOKUP(A1,C:D,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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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

Where Hn = column headers

To lookup Joe and H2:

=VLOOKUP("Joe",A1:D3,MATCH("H2",A1:D1,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?

Thanks for your help.

"Sheeloo" wrote:

Suppose you are using
=VLOOKUP(A1,C:D,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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
NM NM is offline
external usenet poster
 
Posts: 51
Default 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

Where Hn = column headers

To lookup Joe and H2:

=VLOOKUP("Joe",A1:D3,MATCH("H2",A1:D1,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?

Thanks for your help.

"Sheeloo" wrote:

Suppose you are using
=VLOOKUP(A1,C:D,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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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
to figure it out in your head or using some sort of offset adjustment.

--
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

Where Hn = column headers

To lookup Joe and H2:

=VLOOKUP("Joe",A1:D3,MATCH("H2",A1:D1,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?

Thanks for your help.

"Sheeloo" wrote:

Suppose you are using
=VLOOKUP(A1,C:D,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!







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLOOKUP ERROR DeVoe Excel Worksheet Functions 3 April 11th 07 09:11 PM
Vlookup value not available error [email protected] Excel Discussion (Misc queries) 1 December 30th 06 10:44 PM
Vlookup Error, please help PaulW Excel Discussion (Misc queries) 3 April 18th 06 01:32 PM
vlookup error!! Samantha Excel Worksheet Functions 1 April 11th 05 11:02 AM
vlookup error Josh O. Excel Worksheet Functions 6 December 30th 04 06:16 PM


All times are GMT +1. The time now is 09:30 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"