MIN Function with arguments

[Freshman]

Dear experts,

In a worksheet, column A is for player's name and column B is for player's
scores. I list some examples below:

John 25
Peter 65
May 37
Tim 81
Peter 50
Peter 71
May 68
John 57

In C1 is for a lookup value, D1 is for the MAX value of C1 and E1 is for the
MIN value of C1. So, when I input Peter in C1, then D1 will show 71 and E1
will show 50. In such a case, what should be the formula in D1 and E1? Please
kindly advise.

Thanks in advance.

 

[Ashish Mathur]

Hi,

In cell D1, use the following function - =MAX(IF(($A$1:$A$8=C1),$B$1:$B$8)).
Confirm the formula with a Ctrl+Shift+Enter. In cell E1, enter the
following formula - =MIN(IF(($A$1:$A$8=C1),$B$1:$B$8)). Confirm the formula
with a Ctrl+Shift+Enter.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Max]

Assume data as posted within A1:B8

Input in C1, eg: Peter
In D1, array-entered*: =MAX(IF(A1:A8=C1,B1:B8))
In E1, array-entered*: =MIN(IF(A1:A8=C1,B1:B8))
Adapt the ranges to suit your actual data extents

*Press CTRL+SHIFT+ENTER to confirm the formula
instead of just pressing ENTER
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:56
xdemechanik

 

[Mike H]

Try

=MIN(IF($A$1:$A$8=$C$1,$B$1:$B$8,FALSE))
=MAX(IF($A$1:$A$8=$C$1,$B$1:$B$8,FALSE))

Both of these ar array formula so commit with CTRL+Shift+Enter NOT just
enter. If you do it correctly Excel will put curly brackets around the
formula {}. You can't type these yourself.

Mike

 

[Teethless mama]

In D1: =MAX(INDEX((A1:A8=C1)*B1:B8,))
In E1: =MIN(INDEX(10^10-(A1:A8=C1)*(10^10-B1:B8),))

just press ENTER

 

[Freshman]

Hi Max,

Long time no see. Thanks for your tips again. Best regards to you and your
family.

 

[Freshman]

Hi Ashish,

Thanks for your tips. Have a nice day.

 

[Freshman]

Hi Mike,

Nice with your help and tips. Good day. Cheers.

 

[Freshman]

Hi Mama,

I love your name and thanks for your help. Good health and good luck to you.
Bye.

 

[Max]

Welcome, Freshman
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800, Files:359, Subscribers:56
xdemechanik