# MAX IF partial text met using multiple data columns

1. ## 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?

2. ## Re: MAX IF partial text met using multiple data columns

ARRAY formula in H8

=MAX(IF(ISNUMBER(FIND("C",UPPER(A2:A93))),B2:C93))

3. ## Re: MAX IF partial text met using multiple data columns

Or try:

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

4. ## 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

5. ## 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. ## 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. ## Re: MAX IF partial text met using multiple data columns

Post a file as it worked for me.

8. ## Re: MAX IF partial text met using multiple data columns

See attached:

9. ## 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. ## Re: MAX IF partial text met using multiple data columns

I'm an idiot! That worked

Thank you!!

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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