+ Reply to Thread
Results 1 to 10 of 10

Assistance needed with looking up names (Vlookup? Match?)

  1. #1
    Registered User
    Join Date
    06-02-2021
    Location
    England
    MS-Off Ver
    MS 2016
    Posts
    5

    Question Assistance needed with looking up names (Vlookup? Match?)

    Hi there, excel gurus!

    I have two columns. For simplicitlys sake, I have attached two columns of dummy data to display the various types of names in a simple manner.

    In the below workbook, there are a list of names in column 2. I would like to lookup each name in column 2 and see if it exists in column 1. If it does it exist, I would like an output of "TRUE." If it does not exist, I would like an output of "FALSE."

    In my below workbook, each name in column 2 should output "TRUE." Each name in column 2 MATCHES a name in column 1. (In my real-life data - This is the problem. Every name has a match, but some names contain the person's middle name in column 1 but not column 2, and vice versa.

    For example:
    Doe, John should match with Doe, John
    Jones, Adam Michael should match with Jones Michael
    Brown, Marcus Johnson should match with Brown, Marcus

    In the real data, the data is scrambled; the names are not matching neatly side-by-side. I am thinking I need to utilize possibly a Lookup or Match formula or Countif formula, maybe with a wildcard?

    Initially, I tried a simple IFERROR VLookup with a wildcard; but this formula was unable to match up "Brown, Marcus" and "Brown, Marcus Johnson"

    Perhaps this is an easy problem and I'm just not building the correct Lookup formula to catch these variations in Last names, First Names, and Middle Names.

    I appreciate any help you can offer!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Assistance needed with looking up names (Vlookup? Match?)

    Take a look at this updated file:

    I think this may work.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    If you find the suggestion or solution helpful, please consider adding reputation to the post.

  3. #3
    Registered User
    Join Date
    06-02-2021
    Location
    England
    MS-Off Ver
    MS 2016
    Posts
    5

    Re: Assistance needed with looking up names (Vlookup? Match?)

    Hi cubangt thank you for the response.

    That wasn't quite right.

    Doe, John; Jones, Michael; and Brown, Marcus Johnson should all return "TRUE"

  4. #4
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Assistance needed with looking up names (Vlookup? Match?)

    How are you wanting to match them then?
    I would think that for sure Jones, Adam Michael would not match Jones, Michael since the last name is Jones and First name is Adam, not michael.

    If the names are in at least a structure format meaning it will always be last name, first name and middle name, then maybe it can be done. But if there is a chance that the middle name and first name may alternate positions, may be a little harder...

    In a perfect word you would have 3 columns each containing each part of the name.

    Last, First, Middle

    knowing what name is what will help apply the match formula to check if true..

  5. #5
    Registered User
    Join Date
    06-02-2021
    Location
    England
    MS-Off Ver
    MS 2016
    Posts
    5

    Re: Assistance needed with looking up names (Vlookup? Match?)

    Thanks for the reply cubangt. Actually, I do want "Jones, Adam Michael" to match "Jones, Michael"

    In all 3 of the names, a "TRUE" should return. They are the same name, just in one column, the user's middle name is added in but they are the same person. That is what I'm having trouble with.

    So for example, in my dataset, Adam is considered the middle name so these are the same person and I would like a "TRUE" to return.

    We could split the columns into 3....I just don't know if that would make things more complicated.

  6. #6
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Assistance needed with looking up names (Vlookup? Match?)

    Try this in your test file C2:

    Please Login or Register  to view this content.
    Or

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    06-02-2021
    Location
    England
    MS-Off Ver
    MS 2016
    Posts
    5

    Re: Assistance needed with looking up names (Vlookup? Match?)

    Cubangt, your second formula worked like a charm.

  8. #8
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Assistance needed with looking up names (Vlookup? Match?)

    Awesome, glad it helped.

  9. #9
    Registered User
    Join Date
    06-02-2021
    Location
    England
    MS-Off Ver
    MS 2016
    Posts
    5

    Re: Assistance needed with looking up names (Vlookup? Match?)

    Hi Cubangt, I'm sorry for speaking too soon. The second formula isn't actually working. If I change the range to $A:$A and I type in some new names, it starts returning "TRUE" when it should be returning "FALSE." Looks like we will have to keep trying......
    Attached Files Attached Files

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Assistance needed with looking up names (Vlookup? Match?)

    The range needs to start at A2:

    =IF(IFNA(MATCH(B2,$A2:$A2000,-1),FALSE)=FALSE,FALSE,TRUE)

    Make the range as long as necessary,but don't use A:A.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Assistance needed with Index/Match to display a list
    By ExarchMystix in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-06-2019, 12:32 PM
  2. [SOLVED] Vlookup formula - assistance needed
    By abdumon7 in forum Excel General
    Replies: 3
    Last Post: 01-23-2018, 07:39 AM
  3. [SOLVED] Need assistance with VLOOKUP and MATCH function
    By vlaxo085 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-02-2016, 06:49 PM
  4. vlookup for finding out wrongly spelled names and yet match the match
    By catchnanan in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-03-2014, 04:29 PM
  5. [SOLVED] Index/Match assistance needed for search spreadsheet
    By rajncajn in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-18-2013, 02:36 PM

Tags for this Thread

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