Advanced vlookup or Macro?

    Hi everyone -


    I'm working on a project for my youth group and I'm a bit stumped. Hoping someone can help here.


    I have 2 columns in the attached (look at the first tab).


    I want to do a vlookup to match if one cell contains another. Because vlookup right now only looks at exact match, I need partial matches. I'm guessing a Macro would work since it's similar to your Ouija board because it breaks up the words.


    So in this example I want to look at column B and then look at column E to find partial matches.


    Example: B501 has "John Bosco" so does e27. So I want a "vlookup" of some sort on column C 501 to say "John Bosco" or return "cell 327"


    As you can see the actual text of the two cells are different, but they have "John Bosco" in common.


    I know this can get complicated but as long as 1 word matches another I'm okay with it because it saves me 80% of the time, and I can check the rest. The key is for it to show all the matches, not just one.


    Vlookup has to match exactly and returns only 1 answer. I need it to match partially and return all the partial matches...


    Any help would be appreciated

    There are probably many ways to do this but without going into some possibly complex macros, you could get a reasonable result by splitting the names using Text to Columns and then using VLookup.


    Once you have split the names into separate columns, you can match the first name in the list with the first name in your subset (assuming I've understood this correctly).
    As you've indicated, it won't be a perfect match but it will be a start.


    Example of how to do this for eg, January is attached, instructions are on the January tab.

    You're amazing thank you! I think it will cut about 50% of my time now. One tiny request that will make it save me 80% of the time, instead of returning "match" can it return the actual cell number it's in so I don't have to hunt for it?


    Thanks!

    OK, to do that requires a bit more messing about as there could be multiple matching entries.


    In the revised example attached, the column containing the Match/No Match has been revised to return the name if there is a match.


    There are 5 new columns after this which use an array formula to return the cell address of where that name matches in your subset of names.
    This means up to 5 matching cell addresses can be returned.


    You can use the Ctrl+G or F5 key to jump to the cell referenced in the Match Cell columns.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login