VLOOKUP #N/A Error

[NM]

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!

 

[Don Guillett]

Look in the help index for ISNA

 

[Sheeloo]

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.

 

[franciz]

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]

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.

 

[T. Valko]

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",A13,MATCH("H2",A11,0),0)

Result = b

 

[NM]

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]

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.