vlookup does not work

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
DJC said:
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?
Click to expand...

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

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

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
 
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
 
Rob: I have subsequently used the trim function and everthing works as it
should. Thanks for your help. DJC
 
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
 
Back
Top