V Look Up Nightmare

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
 
B

Bernard V Liengme

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

Bernard
 
M

Mel

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

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

HyperKniGHt

Don't forget the fact that the range you loopup the value
into has to be sorted, otherwise the function will never
work...that's why i hate it so much :)

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

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

serhat

Select the column where you are searching for teh URN and go to Data
Text to Columns > Finish.

That should fix it. If that doesnt then do the Trim function as advise
by Kabel
 
D

Don Guillett

It might help to read the help to see what the false parameter does.

--
Don Guillett
SalesAid Software
[email protected]
HyperKniGHt said:
Don't forget the fact that the range you loopup the value
into has to be sorted, otherwise the function will never
work...that's why i hate it so much :)

-----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
Click to expand...


.
Click to expand...
Click to expand...
 
Top