0

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.