+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 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,454

    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,454

    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
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    14,340

    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 * 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
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    14,340

    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: 3917
Size:  47.4 KBName:  VLOOKUP_2.jpg
Views: 3754
Size:  46.5 KBName:  VLOOKUP_3.jpg
Views: 3765
Size:  46.9 KBName:  VLOOKUP_4.jpg
Views: 3783
Size:  82.2 KBName:  VLOOKUP_5.jpg
Views: 3777
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: 3762
Size:  38.5 KB

+ Reply to Thread
Page 1 of 2 1 2 LastLast

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