+ Reply to Thread
Results 1 to 7 of 7

help with finding max if cell contains partial text

  1. #1
    Registered User
    Join Date
    04-14-2017
    Location
    Plymouth, minnesota
    MS-Off Ver
    2016
    Posts
    35

    help with finding max if cell contains partial text

    Looking for a max if formula to find the max value if a cell contains certain text. In the example below I'd like cells C and D to return the max.


    A B C D
    47-3 4,000 Max of column B if column A contains "47" Max of column B if column A contains "43"
    47-1 3,000 4,000 3,000
    43-2 3,000
    43-1 2,000
    47-2 2,000
    47-2 1,000
    43-3 1,000
    43-3 500


    I've tried this formula but it doesn't seem to work.
    {=MAX(IF(A2:A8="*47*",B2:B8,))}

  2. #2
    Registered User
    Join Date
    04-14-2017
    Location
    Plymouth, minnesota
    MS-Off Ver
    2016
    Posts
    35

    Re: help with finding max if cell contains partial text

    A B C D
    47-3 4,000 Max of column B if column A contains "47" Max of column B if column A contains "43"
    47-1 3,000 4,000 3,000
    43-2 3,000
    43-1 2,000
    47-2 2,000
    47-2 1,000
    43-3 1,000
    43-3 500

  3. #3
    Registered User
    Join Date
    04-14-2017
    Location
    Plymouth, minnesota
    MS-Off Ver
    2016
    Posts
    35

    Re: help with finding max if cell contains partial text

    Tried attaching example.
    Attached Files Attached Files

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: help with finding max if cell contains partial text

    Try this:

    =MAX(IF(ISNUMBER(FIND(47,A2:A8)),B2:B8)) Ctrl Shift Enter

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: help with finding max if cell contains partial text

    After seeing the sample workbook, this will work as well:

    =MAX(IF(LEFT(A2:A75,2)="47",B2:B75)) Ctrl Shift Enter

    Note that you will have to adjust the range in the formula from post #4. I was basing that off of what you shared in post #1.

  6. #6
    Registered User
    Join Date
    04-14-2017
    Location
    Plymouth, minnesota
    MS-Off Ver
    2016
    Posts
    35

    Re: help with finding max if cell contains partial text

    Is there a formula that will work without the Ctrl Shift Enter?

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: help with finding max if cell contains partial text

    Sure. You can use this:

    =AGGREGATE(14,6,B2:B75/(LEFT(A2:A75,2)="47"),1)

    Since you are using Excel 2010 or newer.

+ 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. Excel database - Finding Partial text though search tool
    By Cont-Kevin in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 06-15-2017, 09:56 AM
  2. finding partial text in an index match formula
    By garyaw in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 09-30-2015, 04:27 PM
  3. [SOLVED] Finding Partial Text and same number
    By JasonNeedsHelp in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-27-2015, 02:57 AM
  4. Replies: 11
    Last Post: 02-04-2015, 01:34 PM
  5. [SOLVED] Finding partial text matches compared to a range
    By grifta67 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-17-2014, 04:38 PM
  6. [SOLVED] finding partial text ,In the case of more than one match
    By fartdog01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-20-2014, 09:20 AM
  7. matching partial text within one cell to partial text within another
    By Solstice in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 03-03-2010, 09:13 PM

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