Show min and max
 

Hello,

Thanks in advance for the help.

In column A I have drivers names listed, their names are listed multiple
times. In column B I have odometer reading from each time they fill up with
gas. So the sheet will look like this:

NELSON 16977
PIN 13484
COED 20180
KING 44480
VAN 57243
COED 20790
COED 20419
NELSON 17280
ANGELL 13920
POGG 8266
GILLES 17254
KING 44611

On another sheet I have the drivers names listed in column A (their names
are only listed once on this sheet. In column B I would like a formula that
will match the name and return the odometer's min number and in column C
return the odometer's max number.

I appreciate the help. Thank you very much.

Jim

Show min and max
 

Jim,

This assumes your list of names/odometer readings are on sheet 1 and the
name you want is in a1 of the active sheet.

=MIN(IF(Sheet1!$A$1:$A$12=A1,Sheet1!$B$1:$B$12))

=MAX(IF(Sheet1!$A$1:$A$12=A1,Sheet1!$B$1:$B$12))

Both of these are array formula so look below on how to enter

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.


Mike

"Jim" wrote:

Hello,

Thanks in advance for the help.

In column A I have drivers names listed, their names are listed multiple
times. In column B I have odometer reading from each time they fill up with
gas. So the sheet will look like this:

NELSON 16977
PIN 13484
COED 20180
KING 44480
VAN 57243
COED 20790
COED 20419
NELSON 17280
ANGELL 13920
POGG 8266
GILLES 17254
KING 44611

On another sheet I have the drivers names listed in column A (their names
are only listed once on this sheet. In column B I would like a formula that
will match the name and return the odometer's min number and in column C
return the odometer's max number.

I appreciate the help. Thank you very much.

Jim

Show min and max
 

Mike,

I'm getting #N/A, does it make a difference that part of the lookup range
will have blanks or other formula's



"Mike H" wrote:

Jim,

This assumes your list of names/odometer readings are on sheet 1 and the
name you want is in a1 of the active sheet.

=MIN(IF(Sheet1!$A$1:$A$12=A1,Sheet1!$B$1:$B$12))

=MAX(IF(Sheet1!$A$1:$A$12=A1,Sheet1!$B$1:$B$12))

Both of these are array formula so look below on how to enter

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.


Mike

"Jim" wrote:

Hello,

Thanks in advance for the help.

In column A I have drivers names listed, their names are listed multiple
times. In column B I have odometer reading from each time they fill up with
gas. So the sheet will look like this:

NELSON 16977
PIN 13484
COED 20180
KING 44480
VAN 57243
COED 20790
COED 20419
NELSON 17280
ANGELL 13920
POGG 8266
GILLES 17254
KING 44611

On another sheet I have the drivers names listed in column A (their names
are only listed once on this sheet. In column B I would like a formula that
will match the name and return the odometer's min number and in column C
return the odometer's max number.

I appreciate the help. Thank you very much.

Jim

Show min and max
 

Jim,

Try as I might I can't make that formula produce #N/A and no it doesn't
matter if there are formula in the lookup range

Post the precise formula you used.

Mike

"Jim" wrote:

Mike,

I'm getting #N/A, does it make a difference that part of the lookup range
will have blanks or other formula's



"Mike H" wrote:

Jim,

This assumes your list of names/odometer readings are on sheet 1 and the
name you want is in a1 of the active sheet.

=MIN(IF(Sheet1!$A$1:$A$12=A1,Sheet1!$B$1:$B$12))

=MAX(IF(Sheet1!$A$1:$A$12=A1,Sheet1!$B$1:$B$12))

Both of these are array formula so look below on how to enter

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.


Mike

"Jim" wrote:

Hello,

Thanks in advance for the help.

In column A I have drivers names listed, their names are listed multiple
times. In column B I have odometer reading from each time they fill up with
gas. So the sheet will look like this:

NELSON 16977
PIN 13484
COED 20180
KING 44480
VAN 57243
COED 20790
COED 20419
NELSON 17280
ANGELL 13920
POGG 8266
GILLES 17254
KING 44611

On another sheet I have the drivers names listed in column A (their names
are only listed once on this sheet. In column B I would like a formula that
will match the name and return the odometer's min number and in column C
return the odometer's max number.

I appreciate the help. Thank you very much.

Jim