+ Reply to Thread
Results 1 to 13 of 13

Variation of VLOOKUP that returns ALL matches, not just the first one...

  1. #1
    Registered User
    Join Date
    01-22-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    5

    Variation of VLOOKUP that returns ALL matches, not just the first one...

    Hi,
    Please can anyone tell me if there is a version of the VLOOKUP which will return all matches, and not just the first one.

    For example, please see the attachment.
    The second sheet ('Cities') has a list of countries and cities.
    The first sheet ('Country') contains the VLOOKUP function, but it will only return the first match it finds.

    What function do i need to use so that what is returned is EVERY match.
    e.g. If I enter 'Italy' , I want it to return 'Rome' and 'Venice' (either in adjacent cells in the same row, or in the same cell, for example).

    Thanks.
    Attached Files Attached Files
    Last edited by Leicester; 01-22-2013 at 04:53 PM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,442

    Re: Variation of VLOOKUP that returns ALL matches, not just the first one...

    Here is a solution that I have used.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    01-22-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Variation of VLOOKUP that returns ALL matches, not just the first one...

    Quote Originally Posted by alansidman View Post
    Here is a solution that I have used.
    Thanks - my version of excel (2010) does not have a function called MLOOKUP.

    What do I do?
    Last edited by Leicester; 01-22-2013 at 05:08 PM.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,442

    Re: Variation of VLOOKUP that returns ALL matches, not just the first one...

    With the file Mlookup open, click on Alt F11. This will open the VBA section of Excel. In the left VBA Project window, click on the module, it will open the code to create the UDF (User Defined Function) Mlookup. Copy it and paste it into your personal.xlsb file or the file you wish to use it in a new module. Save and close the VBA window. This function will now be available to you in your regular excel function under the topic UDF. I have done this for you in the attached.

    Alan

    Edit: if you want this available to you globally, you will need to create a personal.xlsb file. Look at this link:

    http://www.rondebruin.nl/personal.htm

    Good luck on taking this next step in Excel.
    Attached Files Attached Files
    Last edited by alansidman; 01-22-2013 at 05:44 PM.

  5. #5
    Registered User
    Join Date
    01-22-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Variation of VLOOKUP that returns ALL matches, not just the first one...

    I've created the module and saved it but I still do not have a function called MLOOKUP which I can use.
    I'm clearly out of my depth, but thank you for your help.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,442

    Re: Variation of VLOOKUP that returns ALL matches, not just the first one...

    Did you look in the file I attached. Also, if you installed it, click on the fx in the function window. Then select the section in the drop down called UDF. You should see an MLookup there. In your original file, you may have to change the file name to an xlsm file which is macro enabled. Will probably not work in an xlsx file.


    Edit note: See attached. I double checked and reloaded the module. It does work in this spreadsheet.
    Attached Files Attached Files
    Last edited by alansidman; 01-22-2013 at 06:50 PM.

  7. #7
    Registered User
    Join Date
    01-22-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Variation of VLOOKUP that returns ALL matches, not just the first one...

    Quote Originally Posted by alansidman View Post
    Did you look in the file I attached. Also, if you installed it, click on the fx in the function window. Then select the section in the drop down called UDF. You should see an MLookup there. In your original file, you may have to change the file name to an xlsm file which is macro enabled. Will probably not work in an xlsx file.


    Edit note: See attached. I double checked and reloaded the module. It does work in this spreadsheet.
    That's fantastic. It's exactly what I needed. I think I understand it now.
    Now I feel guilty because you did all the hard work.
    Thanks again.

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,442

    Re: Variation of VLOOKUP that returns ALL matches, not just the first one...

    No problem with the heavy lifting. Now that you understand, the next time will be easier. Excel is always a learning experience.

  9. #9
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    Re: Variation of VLOOKUP that returns ALL matches, not just the first one...

    also it should be noted that if you cannot use macros this can be done like this


    Countries and Cities.xlsx
    The Importance of INDEX - A GUIDE TO INDEX'S OTHER USES
    <--- If a post helps hit the star

  10. #10
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Variation of VLOOKUP that returns ALL matches, not just the first one...

    @ twiggywales

    As the poster has Excel 2010, they can use the AGGREGATE function, which has the SMALL function. So the resulting formula is a non array.
    =IFERROR(INDEX(Cities!$B$1:$B$15,AGGREGATE(15,6,ROW(Cities!$A$1:$A$15)/(Cities!$A$1:$A$15=$A2),COLUMN(A$1))),"")
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)

  11. #11
    Registered User
    Join Date
    02-04-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Variation of VLOOKUP that returns ALL matches, not just the first one...

    Quote Originally Posted by alansidman View Post
    Did you look in the file I attached. Also, if you installed it, click on the fx in the function window. Then select the section in the drop down called UDF. You should see an MLookup there. In your original file, you may have to change the file name to an xlsm file which is macro enabled. Will probably not work in an xlsx file.


    Edit note: See attached. I double checked and reloaded the module. It does work in this spreadsheet.
    Hi

    Just wondering if anyone would know how to modify this VBA function to return all partial matches as well as exact matches. The function MLOOKUP works exactly as I would like however I would like it to also pick up partial matches. Alternatively, a function like MLOOKUP that only looks up and lists partial matches would work just as well.

    Thanks.

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,298

    Re: Variation of VLOOKUP that returns ALL matches, not just the first one...

    Hi anthony and welcome to the forum

    I would suggest that you create your own thread, and reference this 1 in your new thread (else it could be considered hi-jacking Leicester's thread (which is a no-no lol)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  13. #13
    Registered User
    Join Date
    02-04-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Variation of VLOOKUP that returns ALL matches, not just the first one...

    Quote Originally Posted by FDibbins View Post
    Hi anthony and welcome to the forum

    I would suggest that you create your own thread, and reference this 1 in your new thread (else it could be considered hi-jacking Leicester's thread (which is a no-no lol)
    Sorry and thanks!

+ 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