+ Reply to Thread
Results 1 to 3 of 3

Finding Closest Match For Rows

  1. #1
    Registered User
    Join Date
    01-28-2020
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2016 - Version 1912
    Posts
    2

    Post Finding Closest Match For Rows

    For each record in the attached table, I'm trying to find the closest matching record within the table for columns E-R. Ideally the result would return the email address (found in column U) of the closest matching record in column w.

    So basically the formula would have to compare one record with the rest of the records in the table to identify whether or not the values for each column in E - R match, then it would sum how many values matched across all those columns, and it would return the email address for the record that had the highest number of matches. We'd want to perform the same comparison for each record in the table so at the end each record would display a match. If a tiebreaker is needed we would compare the number in column S to see which value is closest.
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Finding Closest Match For Rows

    Please try at W2
    =INDEX(U:U,MOD(LARGE(INDEX(MMULT(--($E$2:$R$5=E2:R2),ROW($A$1:$A$14)^0)*10^6-(ABS(S2-$S$2:$S$5))*10^3+ROW($A$2:$A$5),),2),1000))

  3. #3
    Registered User
    Join Date
    01-28-2020
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2016 - Version 1912
    Posts
    2

    Re: Finding Closest Match For Rows

    Thanks! I validated and it looks like that looks like it does the job.

+ 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. Finding Match or Closest match based on Multi Cells
    By Urlryn in forum Excel General
    Replies: 2
    Last Post: 08-30-2019, 07:07 AM
  2. Finding closest match data
    By jinmatahari in forum Excel General
    Replies: 10
    Last Post: 11-29-2018, 09:54 PM
  3. Finding the closest match from 3 corresponding lists
    By simonpreston in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-31-2018, 01:13 PM
  4. Finding the closest match between two columns within a given range.
    By cfletcher in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-11-2012, 10:37 AM
  5. Finding a closest match with more than one criteria
    By Jayana in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-15-2011, 06:45 PM
  6. Finding next closest match
    By nasir3100 in forum Excel General
    Replies: 1
    Last Post: 04-26-2009, 02:04 AM
  7. Finding Closest Match
    By andyiain in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-15-2006, 03:25 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