+ Reply to Thread
Results 1 to 6 of 6

Search Comma Separated List for Nearest Value

  1. #1
    Registered User
    Join Date
    03-16-2016
    Location
    indiana, US
    MS-Off Ver
    2016
    Posts
    33

    Search Comma Separated List for Nearest Value

    Hello,
    I have a project that contains comma separated lists. I need to search the list for the closest value without going over a target value. (i.e. if the target value is 16 and the list contains 5, 20, 15, 17, 32 then it should return 15)

    My actual workbook contains too many comma separated lists for it to be feasible to break them down into columns and go about it that way.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Search Comma Separated List for Nearest Value

    One way, using AGGREGATE & FILTERXML:

    =AGGREGATE(14,6,FILTERXML("<A><B>"&SUBSTITUTE(C4,",","</B><B>")&"</B></A>","//B")/(FILTERXML("<A><B>"&SUBSTITUTE(C4,",","</B><B>")&"</B></A>","//B")<=A4),1)
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Search Comma Separated List for Nearest Value

    Another option
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    03-16-2016
    Location
    indiana, US
    MS-Off Ver
    2016
    Posts
    33

    Re: Search Comma Separated List for Nearest Value

    Both of those solutions worked. Thanks for the help.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Search Comma Separated List for Nearest Value

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Search Comma Separated List for Nearest Value

    Glad to help & thanks for the feedback

+ 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. how to xlookup if the search keys are comma-separated.
    By tubony in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-27-2021, 09:06 PM
  2. Replies: 4
    Last Post: 12-01-2020, 01:20 PM
  3. Replies: 2
    Last Post: 07-26-2019, 12:11 AM
  4. search and delete multiple item separated by a comma
    By BORUCH in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-15-2018, 02:12 AM
  5. [SOLVED] Extract list - Comma separated
    By TheCman81 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-28-2014, 09:29 AM
  6. Search for comma-separated list of items
    By BBerndsen in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-15-2013, 11:16 AM
  7. Going from column to comma separated list...
    By jmboggiano in forum Excel General
    Replies: 1
    Last Post: 03-10-2005, 01:06 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