+ Reply to Thread
Results 1 to 8 of 8

How to do partial match between two columns that contains names

Hybrid View

  1. 02-07-2024, 02:05 AM #1
    jaysbp
    jaysbp is offline
    Registered User
    Join Date
    11-08-2022
    Location
    singapore
    MS-Off Ver
    365
    Posts
    16

    How to do partial match between two columns that contains names

    Hi,

    Firstly, advance thanks in helping me with this issue.
    I have attached the required file to give more context to my issue.
    I want a dynamic formula in column F where columns A and B are being compared (HR maintains different formats of names compared to another system). I would need the closest match to the name reflecting in Column B to be retrieved from column A in column F.
    I have provided an example in my worksheet (cell F9).
    I need to retrieve the closest matching names from column A(after column A and B are compared for partial matches) into column F for me to compare a report .
    Thanks again.
    Attached Files Attached Files
    Last edited by jaysbp; 02-07-2024 at 02:09 AM.
    Reply With QuoteRegister To Reply
  2. 02-07-2024, 02:38 AM #2
    windknife
    windknife is offline
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    2,885

    Re: How to do partial match between two columns that contains names

    Try this in E9.

    =LET(
  a,FILTER(A9:B100,A9:A100<>""),
  MAP(INDEX(a,,1),LAMBDA(z,
    LET(
      b,TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(z,"(",""),")",""),", ",,1),
      c,MAX(MMULT(--ISNUMBER(SEARCH(b,INDEX(a,,2))),    (SEQUENCE(COLUMNS(b)))^0)*1000+SEQUENCE(ROWS(INDEX(a,,1)))),
  INDEX(a,MOD(c,1000),1)
    )
  ))
)
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 02-07-2024, 02:46 AM #3
    jaysbp
    jaysbp is offline
    Registered User
    Join Date
    11-08-2022
    Location
    singapore
    MS-Off Ver
    365
    Posts
    16

    Re: How to do partial match between two columns that contains names

    Oh wow! This is super helpful! Thanks alot.
    Reply With QuoteRegister To Reply
  4. 02-07-2024, 02:48 AM #4
    windknife
    windknife is offline
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    2,885

    Re: How to do partial match between two columns that contains names

    You are welcome.
    -----------------------------------
    If that takes care of your original question, please click on "Thread Tools" from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  5. 02-07-2024, 02:53 AM #5
    DJunqueira
    DJunqueira is offline
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: How to do partial match between two columns that contains names

    Another way.

    Formula: copy to clipboard 
    =LET(a,$A$9:$A$12,c,BYROW(--ISNUMBER(SEARCH(TEXTSPLIT($B9," ",,1),a)),LAMBDA(x,SUM(x))),INDEX(a,XMATCH(MAX(c),c)))
    Attached Files Attached Files
    Last edited by DJunqueira; 02-07-2024 at 02:57 AM.
    Reply With QuoteRegister To Reply
  6. 02-07-2024, 03:35 AM #6
    DJunqueira
    DJunqueira is offline
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: How to do partial match between two columns that contains names

    windknife and jaysbp, you need to see this more extensive test of the formulas.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 02-07-2024, 07:50 AM #7
    jaysbp
    jaysbp is offline
    Registered User
    Join Date
    11-08-2022
    Location
    singapore
    MS-Off Ver
    365
    Posts
    16

    Re: How to do partial match between two columns that contains names

    Thanks DJunqueira for the contribution of your formulas as well. It is indeed helpful to navigate this issue with various ways.
    Reply With QuoteRegister To Reply
  8. 02-07-2024, 08:07 AM #8
    DJunqueira
    DJunqueira is offline
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: How to do partial match between two columns that contains names

    Tks for the feedback, glad to have helped.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Partial Match Between two Lists with names
    By JBPA in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-24-2021, 06:23 AM
  2. Formula to match from 2 different columns on a partial match
    By Robbiedob in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-24-2019, 06:35 PM
  3. [SOLVED] Find partial match between two columns and highlight cells that match
    By TomToms in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-18-2019, 01:07 PM
  4. Compare Date both Full match/ partial match between two columns
    By cyboincomp in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-21-2018, 03:02 AM
  5. Match columns A & C and partial Match B & D and get column E data
    By Excelbanksters in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-26-2016, 04:05 PM
  6. [SOLVED] Partial Match for Group Names
    By Saarang84 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 05-03-2015, 09:32 PM
  7. Trying to match up 2 columns of data on a partial match
    By tmedia in forum Excel General
    Replies: 3
    Last Post: 01-28-2012, 03:41 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1