V Look Up Nightmare

[Mel]

I am having a nightmare with Excel, I am typing in the below formula.

=VLOOKUP(A2,'[Davids File.xls]Sheet1'!$A$2:$B$2792,2,FALSE)

I want to look for a URN in another file hence the Vlookup, but it is
returning a value of "N/A", even though at a closer glance some of the URN's
appear in the spreedsheet.

Please help

 

[Bernard V Liengme]

Hi Mel,
Is it possible that A2 is numeric while the values in the other file are
text?

Bernard

 

[Mel]

no, any ideas?

Hi Mel,
Is it possible that A2 is numeric while the values in the other file are
text?

Bernard

I am having a nightmare with Excel, I am typing in the below formula.

=VLOOKUP(A2,'[Davids File.xls]Sheet1'!$A$2:$B$2792,2,FALSE)

I want to look for a URN in another file hence the Vlookup, but it is
returning a value of "N/A", even though at a closer glance some of the URN's
appear in the spreedsheet.

Please help

 

[Don Guillett]

from help

a.. If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses
the largest value that is less than or equal to lookup_value.
b.. If lookup_value is smaller than the smallest value in the first column
of table_array, VLOOKUP returns the #N/A error value.
c.. If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP
returns the #N/A value.

 

[Frank Kabel]

Hi
as VLOOKUP works to my experience correctly it has something to do with
your data. Try the following:
- locate a match (at least on you would assume it would match) in your
second file. lets say A10
- enter the formula
=A2='[Davids File.xls]Sheet1'!$A$10
Does this return TRUE?. I would assume it will return FALSE. In this
case check the following:
- whaqt kind of data do you have in column A? Text or numbers. For the
latter one check if they are really numbers. Use
=ISNUMBER(A2)
and
=ISNUMBER('[Davids File.xls]Sheet1'!$A$10)
for checking this

For text value look if they have hidden characters or spaces. Try
=TRIM(A2)=TRIM('[Davids File.xls]Sheet1'!$A$10)
does this return TRUE?

 

[Os]

This might not be the issue, but make sure the value in
the Table_arry is on the "A" column. Because I've just
tested your formula and it's working fine for me.

Osman.

-----Original Message-----
no, any ideas?

Hi Mel,
Is it possible that A2 is numeric while the values in the other file are
text?

Bernard

I am having a nightmare with Excel, I am typing in the below formula.

=VLOOKUP(A2,'[Davids File.xls]Sheet1'! $A$2:$B$2792,2,FALSE)

I want to look for a URN in another file hence the Vlookup, but it is
returning a value of "N/A", even though at a closer

glance some of the
URN's

appear in the spreedsheet.

Please help

.