+ Reply to Thread
Results 1 to 10 of 10

MAX IF partial text met using multiple data columns

  1. #1
    Forum Contributor
    Join Date
    03-05-2015
    Location
    norwich
    MS-Off Ver
    365
    Posts
    107

    MAX IF partial text met using multiple data columns

    I've scoured everywhere for this and can only find solutions that use multiple criteria columns and not multiple data columns.

    In the example I essentially want the maximum value (minimum as well but this will hopefully sort that out) of 3 columns of data everytime the letter C is in that row

    Formulas used are

    {=MAX(IF(A2:A9388=G2,B2:C9388))} - works for exact match
    {=MAX(IF(A2:A9388=H2&"*",B2:C9388))} - doesn't work for partial match

    Is the reason it doesn't work because it's an array? Should I try avoid arrays?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,483

    Re: MAX IF partial text met using multiple data columns

    ARRAY formula in H8

    =MAX(IF(ISNUMBER(FIND("C",UPPER(A2:A93))),B2:C93))
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,576

    Re: MAX IF partial text met using multiple data columns

    Or try:

    =MAX(IF(LEFT(A2:A9388,LEN(H2))=H2,B2:C9388))

  4. #4
    Forum Contributor
    Join Date
    03-05-2015
    Location
    norwich
    MS-Off Ver
    365
    Posts
    107

    Re: MAX IF partial text met using multiple data columns

    Both methods work perfectly!

    Thank you

    With regards to min, how would the formula be if I was to ignore all 0 values
    Last edited by mrsak87; 04-21-2018 at 09:50 AM.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,295

    Re: MAX IF partial text met using multiple data columns

    Try

    =MIN(IF((ISNUMBER(FIND("C",UPPER($A$2:$A$93)))*($B$2:$B$93<>0)),$B$2:$C$93))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  6. #6
    Forum Contributor
    Join Date
    03-05-2015
    Location
    norwich
    MS-Off Ver
    365
    Posts
    107

    Re: MAX IF partial text met using multiple data columns

    Thanks for the reply, the formula works like the others but still finds 0 as the lowest number. I can't get it to ignore all 0's

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,295

    Re: MAX IF partial text met using multiple data columns

    Post a file as it worked for me.

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,295

    Re: MAX IF partial text met using multiple data columns

    See attached:
    Attached Files Attached Files

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,632

    Re: MAX IF partial text met using multiple data columns

    Correct slightly:
    =MIN(IF((ISNUMBER(FIND("C",UPPER($A$2:$A$93)))*($B$2:$C$93<>0)),$B$2:$C$93))

  10. #10
    Forum Contributor
    Join Date
    03-05-2015
    Location
    norwich
    MS-Off Ver
    365
    Posts
    107

    Re: MAX IF partial text met using multiple data columns

    I'm an idiot! That worked

    Thank you!!

+ 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] Data Cleansing - Text To Columns With Multiple Columns
    By adam_d_john in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-03-2017, 05:25 PM
  2. Replies: 0
    Last Post: 08-17-2017, 11:26 AM
  3. Replies: 4
    Last Post: 03-15-2017, 03:36 PM
  4. Help with looking up Partial text multiple matches
    By cceze in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-21-2016, 01:06 PM
  5. [SOLVED] How to compare partial text in two columns and return value from adjacent cell
    By Ghys772 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-07-2014, 07:43 AM
  6. Find Partial Text Match between Two Columns of data
    By samybelen in forum Excel General
    Replies: 1
    Last Post: 10-23-2013, 01:45 PM
  7. [SOLVED] Conditional formating based on text in 2 columns (but only partial text!)
    By Icehockey44 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-19-2012, 06:26 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