vlookup does not work

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" <DJC@discussions.microsoft.com> wrote in message
news:3DCCFA43-0535-4753-BABB-186D8A59A14B@microsoft.com...
> 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

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" wrote:

> "DJC" <DJC@discussions.microsoft.com> wrote in message
> news:3DCCFA43-0535-4753-BABB-186D8A59A14B@microsoft.com...
> > 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" <DJC@discussions.microsoft.com> wrote in message
news:6DF10BFF-DC23-4336-A9C8-7FE52D9A58D9@microsoft.com...
> 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

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" wrote:

> "DJC" <DJC@discussions.microsoft.com> wrote in message
> news:6DF10BFF-DC23-4336-A9C8-7FE52D9A58D9@microsoft.com...
> > 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" <DJC@discussions.microsoft.com> wrote in message
news:9E9A4314-9867-4587-AD4A-C43DE0CFEFF6@microsoft.com...
> 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

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 Bovey" wrote:

> "DJC" <DJC@discussions.microsoft.com> wrote in message
> news:9E9A4314-9867-4587-AD4A-C43DE0CFEFF6@microsoft.com...
> > 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
>
>
>

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

"DJC" wrote:

> 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 Bovey" wrote:
>
> > "DJC" <DJC@discussions.microsoft.com> wrote in message
> > news:9E9A4314-9867-4587-AD4A-C43DE0CFEFF6@microsoft.com...
> > > 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
> >
> >
> >

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

"DJC" <DJC@discussions.microsoft.com> wrote in message
news:8D86CE45-4486-408C-A2A0-FA34B0BFDE85@microsoft.com...
> Rob: I have subsequently used the trim function and everthing works as it
> should. Thanks for your help. DJC
>
> "DJC" wrote:
>
>> 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 Bovey" wrote:
>>
>> > "DJC" <DJC@discussions.microsoft.com> wrote in message
>> > news:9E9A4314-9867-4587-AD4A-C43DE0CFEFF6@microsoft.com...
>> > > 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
>> >
>> >
>> >