 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Excel Errors / April 2005vlookup 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? > 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?  SignatureRob 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 > > 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: 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. SignatureRob 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: Yes I have, I'm only getting a value when I leave that variable off > > or [quoted text clipped - 12 lines] > the other and look for a difference out in the further range of decimal > places. > 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. SignatureRob 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 appreciate your response. I believe I understand vlookup from a > > syntax and set up perspective. What I don't understand is what would [quoted text clipped - 15 lines] > match because there is no exact match and that row holds the largest value > that is less than the lookup value. Rob: I have subsequently used the trim function and everthing works as it should. Thanks for your help. 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 [quoted text clipped - 22 lines] > > match because there is no exact match and that row holds the largest value > > that is less than the lookup value. 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. SignatureRob 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 [quoted text clipped - 38 lines] >> > value >> > that is less than the lookup value. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.