vlookup does not work

[DJC]

when I use vlookup the function returns the value from the correct column but
from the row above the row with the search match. what am i doing wrong?

 

[Rob Bovey]

when I use vlookup the function returns the value from the correct column
but
from the row above the row with the search match. what am i doing wrong?

Have you used False as the last argument so that VLookup returns an
exact match?

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[DJC]

Rob: Yes I have, I'm only getting a value when I leave that variable off or
when it's true, false gave me #N/A. I also tried lookup and have the same
issue with thaat function in that it returns a value from the right column
but from the row above the match row. Thanks. Dave

 

[Rob Bovey]

Rob: Yes I have, I'm only getting a value when I leave that variable off
or
when it's true, false gave me #N/A. I also tried lookup and have the same
issue with thaat function in that it returns a value from the right column
but from the row above the match row. Thanks. Dave

Setting the last argument of vlookup to False tells it to find an exact
match. If vlookup returns #N/A when the last argument is False it means the
function could not find an exact match. This means there must be some
difference between the value you are looking for and the value you think
should match it.

If the two values you are trying to match are numeric or date, copy them
and Paste Special/Values into empty cells somewhere. Then subtract one from
the other and look for a difference out in the further range of decimal
places.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[DJC]

Rob: I appreciate your response. I believe I understand vlookup from a
syntax and set up perspective. What I don't understand is what would cause
the function to find the match on row 20 for instance and return the required
value from row 19. Please attempt to address that issue. Thanks.

 

[Rob Bovey]

Rob: I appreciate your response. I believe I understand vlookup from a
syntax and set up perspective. What I don't understand is what would
cause
the function to find the match on row 20 for instance and return the
required
value from row 19. Please attempt to address that issue. Thanks.

I don't believe this is what's happening. Because you have verified that
vlookup is giving you a #N/A error when you use False as the last argument,
you have no exact match. When you omit the last argument or set it to True,
vlookup will return the largest value that is less than the value you're
looking for if it cannot find an exact match.

This only works properly when the lookup column is sorted in ascending
order. Assuming your data is sorted this way, the behavior you are
experiencing is exactly what you would expect if there is no exact match.
Vlookup is returning the data from the row above the one you think should
match because there is no exact match and that row holds the largest value
that is less than the lookup value.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[DJC]

Rob: You're absolutely right. The problem is my values which I have
imported from an AS/400 application came in with spaces to the right of the
number so I was trying to match 1234-56{SPACE}{SPACE}{SPACE} with 1234-56 and
obviously not matching. How do I get rid of the spaces to the right? They
are different amounts in the different values. Thanks. You've been a great
help so far. DJC

 

[DJC]

Rob: I have subsequently used the trim function and everthing works as it
should. Thanks for your help. DJC

 

[Rob Bovey]

Glad you got it to work. My free Excel Utilities package, which you can
download from the web site below my signature, contains a feature that will
trim all spaces from the current selection. You can just select all your
data, choose the Utilities/Selection Utilities/Trim Selection menu and it
will remove all of the trailing spaces from your data without requiring you
to use any worksheet functions.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm