V Look Up Nightmare

  • Thread starter Thread starter Mel
  • Start date Start date
M

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
 
Hi Mel,
Is it possible that A2 is numeric while the values in the other file are
text?

Bernard
 
no, any ideas?

Bernard V Liengme said:
Hi Mel,
Is it possible that A2 is numeric while the values in the other file are
text?

Bernard

Mel said:
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
Click to expand...
Click to expand...
 
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.
 
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?
 
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
Click to expand...
glance some of the
URN's
appear in the spreedsheet.

Please help
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top