# MIN IF Array Function with Multiple 'is like' criteria

I think I have a good one.

I need a MIN-IF (and MAX-IF) on multiple criteria where near matches are required. I know how execute a MIN IF on multiple criteria using the product of many `IF` statements as described here, but I need to use near matches/like matches and `IF` does not accept the `"*" & criteria & "*"` syntax of functions like `COUNTIF, VLOOKUP,...`. I am able to handle the MAX-IF with a `ISNUMBER SEARCH` method.

I wish I could post a picture of my dummy data, but I don't have the rep to do that. So I'll try to describe it best I can.

Column A contains the data for the first criteria in rows 2:14.

Column B contains the data for the second criteria in rows 2:14.

Column C contains the values on which I want to determine the near match `MIN` in rows 2:14.

I have criteria 1 in cell `F1` and criteria 2 in cell `F2`. The formula that I have for the MAX IF is below:

`{=MAX(IF(ISNUMBER(SEARCH(F1,A2:A14)) * ISNUMBER(SEARCH(F2,B2:B14)), ISNUMBER(SEARCH(F1,A2:A14)) * ISNUMBER(SEARCH(F2,B2:B14)))*C2:C14)}`

I tried to exactly replicate this with a `MIN` instead of the `MAX`, but it is just returning `0`.

I am currently using Excel 20113 and am open to worksheet function solutions or VBA based ones. Any thoughts on this one?

Thanks.

You can shorten your max array formula to:

``````=MAX(IF((ISNUMBER(SEARCH(F1,A2:A14))) * (ISNUMBER(SEARCH(F2,B2:B14))),C2:C14))
``````

Next try this for your min:

``````=MIN(IF((ISNUMBER(SEARCH(F1,A2:A14))) * (ISNUMBER(SEARCH(F2,B2:B14))),C2:C14))
``````

If you have 2010 or later you do not need the CSE array formula use these:

Max:

``````=AGGREGATE(14,6,C2:C14/((ISNUMBER(SEARCH(F1,A2:A14)))*(ISNUMBER(SEARCH(F2,B2:B14)))),1)
``````

Min:

``````=AGGREGATE(15,6,C2:C14/((ISNUMBER(SEARCH(F1,A2:A14)))*(ISNUMBER(SEARCH(F2,B2:B14)))),1)
``````

Now the fun part, If you have the latest version of 365 or are using office online then you can use their new MINIFS() and MAXIFS():

``````=MAXIFS(C2:C14,A2:A14,"*" & F1 & "*",B2:B14,"*" & F2 & "*")
=MINIFS(C2:C14,A2:A14,"*" & F1 & "*",B2:B14,"*" & F2 & "*")
``````
• Thank you, Scott. I went with the second option (AGGREGATE) because the MIN-IF CSE function worked well on my dummy data, but not on the actual project so there must have been some missing generalization on my part. Loving the solution, though. Appreciate it. – Nick Criswell Mar 5 '16 at 20:10
• You will find that you have text in the values somewhere or errors in the values. MIN(IF()) breaks on errors. The Aggregate ignores them, this is forced with the second criterion of 6 in the aggregate which tells the formula to ignore errors. @NickC, BTW I was working on this answer when you deleted the question on StackOverflow. – Scott Craner Mar 5 '16 at 20:12
• Understood, makes sense. Sorry about that, Scott. I realized that it didn't involve coding at all so it made more sense on this Stack Exchange. I really apologize for the inconvenience that may have caused. – Nick Criswell Mar 5 '16 at 20:14