min and max value in excel

Question

I want to know how to find the minimum and maximum value in excel sheets using criteria? I try to use this formula but it's not working. I need specific text if is match it will show minimum or maximum value. For your information, I am using Excel 2013.

Answer 1

I believe using a pivot table would be simplest, but that said, you can use the following array formulae (Use Ctrl+Shift+Enter instead of Enter):

=MAX(IF(ISERROR(SEARCH(D1,A2:A6)),MIN(B2:B6),B2:B6))
=MIN(IF(ISERROR(SEARCH(D1,A2:A6)),MAX(B2:B6),B2:B6))

Answer 2

Like

=MAX(IF(IFERROR(FIND("Shoaib",$A$2:$A$6),0)>0,$B$2:$B$6))

And

=MIN(IF(IFERROR(FIND("Shoaib",$A$2:$A$6),0)>0,$B$2:$B$6))

Entered as array formulas with Ctrl + Shift + Enter

---

Data

Answer 3

For purposes of your reporting, one sensible option here would be to just add a filter on the Name column. Then, just take the max and min of the entire Marks column. This would let you quickly obtain the min or max marks for any person using only the Excel GUI. And it avoids the need to edit a formula for each different name.

Answer 4

Input these as ARRAY formula. Means press CTRL+SHIFT+ENTER after putting formula in cell.

=MAX(IF(ISNUMBER(SEARCH("Shoaib",A2:A6)),B2:B6,""))

=MIN(IF(ISNUMBER(SEARCH("Shoaib",A2:A6)),B2:B6,""))