After the column to bring back put false, a true or blank tells excel to get
the closest match.
=VLOOKUP(C1,$A$1:$B$3,2,FALSE)

--
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"Paul Lyons" <Paul wrote in message
...
Hi,

I have noticed 2 things about VLookup which are causing me some grief.

Firstly, for values it cannot look up it seems to be using the last
available value in the lookup column. Example:
Reference columns are the two leftmost.
The formula in the fourth column is: =VLOOKUP(C1,$A$1:$B$3,2)

AA One BB Two
BB Two CC Three
CC Three AA One
BB Two
DD Three
AA One

Note that DD is not defined in the reference list, yet it is still given a
value.


Secondly, the function seems to have a problem when looking up values with
numbers:
Again the leftmost columns contain the reference values and the formula in
the fourth column only differs according to the new range of these values.

AA One BB Two
BB Two 1A #N/A
CC Three AA One
1A Four BB Two
CC Three

Note the value 1A does not get "lookup up". This occurs even when the
columns are formatted as Text.

I am using Excel 2003 SP2 running on Windows XP and have checked the issue
on Excel 2002 SP1 running Windows 2000.

Is this a known issue? Is there a work around for it?
The values I am looking are to compare data from two databases so there's
no
way I can change the values and still be confident of my reconciliation..


Thanks in advance
Paul