Vlookup returns value in the adjacent row

[Bran]

I am trying to use the vlookup function and it is returning the value in the
cell in the row just above the appropriate value in the table I am searching.

For example, when my lookup_value corresponds to A3 (in the table below),
the function is returning B2:
A1 B1
A2 B2
A3 B3
A4 B4

Just in case this matters, the array has been copied from Access (but it's
in an Excel worksheet). Also, the values in the lookup column are in 2 forms
(#1 form 123456, #2 form 234-567). I have tried converting everything to
text and to general numbers but it doesn't seem to help.

Any help would be appreciated.
Bran

 

[tim m]

Perhaps you could post the actual VLOOKUP formula so people can have a look
see, might be easier to see what the probem might be.

 

[Tom Hutchins]

VLOOKUP accepts an argument which I suspect you are not providing. This
argument, called Range_lookup, determines if VLOOKUP returns an exact match
or an approximate match. Here is the Excel Help info about Range_lookup:

Range_lookup is a logical value that specifies whether you want VLOOKUP to
find an exact match or an approximate match. If TRUE or omitted, an
approximate match is returned. In other words, if an
exact match is not found, the next largest value that is less than
lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one
is not found, the error value #N/A is returned.

Add a FALSE Range_lookup argument to your VLOOKUP calls, and I think your
problem may be solved. Also, by specifying that you want an exact match, the
range you are searching need not be sorted first.

Hope this helps,

Hutch