+ Reply to Thread
Results 1 to 27 of 27

Advanced VLOOKUP

  1. #1
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Advanced VLOOKUP

    The VLOOKUP function is, IMHO, one of the most used functions in Excel. Even so, spelling mistakes and extra spaces can throw it off completely. Here's my simple solution for dealing with extra spaces.

    Tip #1)
    N.B. Intentional extra spaces have been put before, in the middle, and after each name:


    Instead of:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    You can use:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    entered as Ctrl+Shift+Enter

    Tip #2)
    Now you might only know one name (e.g. John or Doe) but not the other name (ie. partial match). Then you can use a little semi-fuzzy logic to get the answer:


    Formula: copy to clipboard
    Please Login or Register  to view this content.

    N.B. The above formula assumes that you are only looking up one word (e.g. no spaces).


    If you are looking for a partial match with more than one word (e.g. Joh D) then use this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    entered as Ctrl+Shift+Enter

    Tip #3)
    If you are having problems with spelling mistakes, etc. then you need to get really fuzzy. Alan has provided some awesome code for doing Fuzzy-VLookup including Soundex and Metaphone similarities.

    If your boss is against using non-Microsoft add-ins or vba macros/functions then you can rest assured that MS has jumped on the bandwagon and now has its own version of Fuzzy matching for VLookup. You can find the add-in here, but be warned that it only works on tables and only officially with Excel 2010. Debra has created a page on this topic and one of the comments posted is that it also works on Excel 2007. Haven't tried it except on Excel 2010 so can't comment.

    Tip #4)
    One of the greatest limitations of VLookup is that is only looks up in one direction (to the right) of any said value. Looking at the example below, if you wanted to use an account number to find out the rest of the details of a purchase you woud not be able to do so with the traditional vlookup formula since the account numbers are not the first column in the lookup table.


    This tip is actually two in one:

    Part 1. If you only wanted to know one piece of information that is related to your lookup value then you can use a combination of Choose and VLookup:


    Formula: copy to clipboard
    Please Login or Register  to view this content.

    this tricks vlookup into switching the columns around and so finds the account number in Column B and returns the associated value from Column A. This technique is detailed here (http://www.myonlinetraininghub.com/e...t-using-choose).

    Part 2. Often this is not enough and you would like to be build a complete retrieval system for the information in the VLookup table as you would with a traditional VLookup formula. This can be accomplished by taking the above formula one step further by creating a nested VLookup formula where the inner VLOOKUP will provide the lookup value for the outer VLOOKUP. If anyone knows of any references for this I will be happy to post it as I actually thought of this by myself (not saying I was the first, but I haven't seen anyone else use this technique before).


    Names:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Order details:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Order status:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Hope this helps.

    abousetta
    Attached Files Attached Files
    Last edited by abousetta; 07-19-2012 at 08:26 AM. Reason: Added pics
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  2. #2
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Advanced VLOOKUP

    Of course any suggestions on improvement are highly desirable and welcomed.

    Thanks.

    abousetta
    Last edited by abousetta; 07-07-2012 at 09:49 AM. Reason: Like button has appeared now

  3. #3
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,452

    Re: Advanced VLOOKUP

    I think, in tip #2, you meant to say "Now you might know only one name ...." instead of what you did say "Now you might not know only one name ......"

  4. #4
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Advanced VLOOKUP

    Thanks Cutter. You are right.

  5. #5
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,452

    Re: Advanced VLOOKUP

    Glad to help. And I see the Like Button is back so I "liked" it.

  6. #6
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Advanced VLOOKUP

    Thanks Cutter, I have amended my earlier post. I think the issue is that I can't see the 'Like' button for my own posts and that's why I was confused.

    Thanks again for the vote.

    abousetta

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,231

    Re: Advanced VLOOKUP

    Hi,

    In tip #2 the

    =VLOOKUP("*"&TRIM(D2)&"*",TRIM(A1:B4),2,0)

    You don't need the TRIM(A1:B4) as the "*Trim(D2)*" doesn't care if it is trimmed or not.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  8. #8
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Advanced VLOOKUP

    Good point Marvin. I have updated Tip #2.

  9. #9
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Advanced VLOOKUP

    Not necessarily - TRIM also removes excessive spaces between words, not just at the beginning and end.
    Remember what the dormouse said
    Feed your head

  10. #10
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Advanced VLOOKUP

    Thanks for that RS. In fact we are all right depending on the lookup value (single word versus multiple words). I have returned the version using trim as a variant.

  11. #11
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,231

    Re: Advanced VLOOKUP

    @abousetta,

    Beware of the TRIM() function. In some programs and/or versions it DOES NOT reduce multiple spaces to a single space when "inside" the string. I would need to research this a little more but I believe older versions of Excel didn't reduce double spaces to a single. I would also worry about VBA vs Excel TRIM(). Anybody up for running this down? I also believe there are little differences between Access and Excel with this middle spaces question.

  12. #12
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Advanced VLOOKUP

    I heard about the difference between Excel function Trim and vba Trim. vba Trim only clears the leading and end spaces, while the function removes multiple spaces between words also. It was even suggested in a previous post by one of the MVPs to use application.trim() in vba if the need appears.

    As for using it in earlier versions, I don't know about any changes made to this function during the version upgrades, but it is possible.

  13. #13
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Advanced VLOOKUP

    Two new tips have been added to the first post along with pics and a sample workbook.

    Hope this helps.

    abousetta

  14. #14
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Advanced VLOOKUP

    This post is only to allow me to show the figures in post #1Name:  VLOOKUP_1.jpg
Views: 3952
Size:  47.4 KBName:  VLOOKUP_2.jpg
Views: 3797
Size:  46.5 KBName:  VLOOKUP_3.jpg
Views: 3808
Size:  46.9 KBName:  VLOOKUP_4.jpg
Views: 3822
Size:  82.2 KBName:  VLOOKUP_5.jpg
Views: 3816
Size:  14.9 KB

  15. #15
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Advanced VLOOKUP

    This post is only to allow me to show the figures in post #1Name:  VLOOKUP_6.jpg
Views: 3797
Size:  38.5 KB

  16. #16
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Advanced VLOOKUP

    OK. I think the pics are now visible in the first post. If anyone can't see them, then please let me know and I will investigate it further.

    Thanks.

    abousetta

  17. #17
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: Advanced VLOOKUP

    You can also add VLOOKUP function using range within a function:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  18. #18
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Advanced VLOOKUP

    Hi Zbor,

    Could you explain this a bit because I'm not sure I understand the formula you posted? Also it's giving me an error (in error):

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Thanks.

    abousetta

  19. #19
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: Advanced VLOOKUP

    Quote Originally Posted by abousetta View Post
    Hi Zbor,
    Could you explain this a bit because I'm not sure I understand the formula you posted? Also it's giving me an error (in error):
    There could be regional setting problem. In above I just replace ; with , but maybe you need to adopt something more.
    Here is original formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    With c in A1 it return testing as expected.

  20. #20
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,573

    Re: Advanced VLOOKUP

    @ abousetta

    Another problem that occurs with VLOOKUP is with numbers - the sought value might be a number but the lookup values in the table might be text values, or viceversa. This can be overcome quite easily like this:

    =VLOOKUP(D1*1,table,2,0)

    or

    =VLOOKUP(D1&"",table,2,0)

    and these can be combined into one formula to make it more universal. Perhaps you might like to cover that in your tips.

    Another problem you might like to deal with is where you have multiple names, e.g. if you had John Lee as the 4th item in your table in Tip #2.

    Hope this helps.

    Pete

  21. #21
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Advanced VLOOKUP

    Thanks zbor, yes I fiddled with it until I got the regional settings correctly

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  22. #22
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Advanced VLOOKUP

    Thanks Pete. I will add this tip also to the first post.

    abousetta

  23. #23
    Registered User
    Join Date
    01-16-2013
    Location
    pakistan
    MS-Off Ver
    2007
    Posts
    1

    Re: Advanced VLOOKUP

    Many thanks for the latest information.You have done such agreat effort .This is so informative tread.

  24. #24
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Advanced VLOOKUP

    Thanks for the feedback. I'm glad that the effort is well-received.

    abousetta

  25. #25
    Registered User
    Join Date
    08-21-2013
    Location
    UT
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Advanced VLOOKUP

    Great post, gotta love the vlookup!

  26. #26
    Forum Contributor
    Join Date
    10-19-2012
    Location
    chennai
    MS-Off Ver
    Excel 2013
    Posts
    134

    Re: Advanced VLOOKUP

    hi all any tip to match the last duplicate value in a range..

  27. #27
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Advanced VLOOKUP

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1