+ Reply to Thread
Results 1 to 2 of 2

Need help with a match formula that returns the greatest value out of the matches.

  1. #1
    Registered User
    Join Date
    07-08-2020
    Location
    Alabama, USA
    MS-Off Ver
    Office 365
    Posts
    1

    Question Need help with a match formula that returns the greatest value out of the matches.

    I am trying to create a formula that will search text and return only the value that has the greatest number at the end, but the rest of the value prior to the last number must match. I have attached a photo below and the file to this post.

    I am trying to search column K and have a formula in column L that returns the same value if the last number in the column K value is the greatest out of any value in column K when the rest of the value matches. I have typed out the result I would like for a few example rows below.
    Excel help1.PNG

    The working formula that I thought would work but is returning the "value" error is. : =IF(MAX(--(LEFT($K$2:$K$5360,16)=LEFT(K2,16))*RIGHT($K$2:$K$5360,1))=--RIGHT(K2,1),K2,"")


    Any help is greatly appreciated!
    Attached Files Attached Files

  2. #2
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,090

    Re: Need help with a match formula that returns the greatest value out of the matches.

    Please try at L2

    =IF(MAX(IF(LEFT($K$2:$K$5360,LEN(K2)-1)=LEFT(K2,LEN(K2)-1),--RIGHT($K$2:$K$5360)))=--RIGHT(K2),K2,"")

    Array formula is slow for large data

    If you can change Revision column to number or use helper column
    M2
    =--D2

    then at N2
    =IF(MAXIFS($M$2:$M$5360,$K$2:$K$5360,LEFT(K2,LEN(K2)-1)&"*")=M2,K2,"")
    This is a lot faster than array formula
    Attached Files Attached Files

+ 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. [SOLVED] Index Match formula to skip blanks and return next greatest value
    By mark_luke in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2019, 02:39 PM
  2. [SOLVED] returning greatest and lowest value involving index match formula
    By bridge4444 in forum Excel General
    Replies: 6
    Last Post: 08-10-2016, 07:04 PM
  3. Replies: 6
    Last Post: 02-06-2016, 05:04 PM
  4. [SOLVED] Array formula that returns the number of the row with the greatest sum
    By Yangado in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-30-2015, 01:30 PM
  5. COUNTIFS issue - Returns zero matches (possibly wrong formula???)
    By sx200n in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-22-2014, 08:09 AM
  6. [SOLVED] INDEX MATCH array formula that matches substring n gives multiple matches
    By bkwins in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-12-2013, 04:57 AM
  7. Replies: 2
    Last Post: 08-16-2012, 09:00 AM

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