I have a large set of data with many entries. I need to find all instances of
any given value in the first column and then return the maximum and minimum
corresponding value in the second column. For example:

110 25.3
111 6.3
113 7.5
110 11.2
112 21.3
113 11.8
114 30.1
110 2.7

In this example I would like to be able to specify a lookup value, 110 for
example and then have a formula/formulae to retrieve the maximum and minimum
value in the second column, 25.3 and 2.7 in this case.
Thanks in advance

Hi

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

Wher C1 is the lookup value (110)

'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

"steve_m" wrote:

I have a large set of data with many entries. I need to find all instances of
any given value in the first column and then return the maximum and minimum
corresponding value in the second column. For example:

110 25.3
111 6.3
113 7.5
110 11.2
112 21.3
113 11.8
114 30.1
110 2.7

In this example I would like to be able to specify a lookup value, 110 for
example and then have a formula/formulae to retrieve the maximum and minimum
value in the second column, 25.3 and 2.7 in this case.
Thanks in advance

I fyour numbers are all positive then min won't work because youwill return
0. try this instead

=MIN(SUBSTITUTE(--($A$1:$A$8=C1),0,1E+99)*$B$1:$B$8)

"Mike H" wrote:

Hi

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

Wher C1 is the lookup value (110)

'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

"steve_m" wrote:

I have a large set of data with many entries. I need to find all instances of
any given value in the first column and then return the maximum and minimum
corresponding value in the second column. For example:

110 25.3
111 6.3
113 7.5
110 11.2
112 21.3
113 11.8
114 30.1
110 2.7

In this example I would like to be able to specify a lookup value, 110 for
example and then have a formula/formulae to retrieve the maximum and minimum
value in the second column, 25.3 and 2.7 in this case.
Thanks in advance

Many thanks.
Steve

"Mike H" wrote:

Hi

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

Wher C1 is the lookup value (110)

'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

"steve_m" wrote:

I have a large set of data with many entries. I need to find all instances of
any given value in the first column and then return the maximum and minimum
corresponding value in the second column. For example:

110 25.3
111 6.3
113 7.5
110 11.2
112 21.3
113 11.8
114 30.1
110 2.7

In this example I would like to be able to specify a lookup value, 110 for
example and then have a formula/formulae to retrieve the maximum and minimum
value in the second column, 25.3 and 2.7 in this case.
Thanks in advance

Hi,

I had assumed all of range populated but in the event of blanks I prefer

=MIN(IF($A$1:$A$8=C1,IF(B1:B8<"",$B$1:$B$8)))

Mike

"Joel" wrote:

I fyour numbers are all positive then min won't work because youwill return
0. try this instead

=MIN(SUBSTITUTE(--($A$1:$A$8=C1),0,1E+99)*$B$1:$B$8)

"Mike H" wrote:

Hi

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

Wher C1 is the lookup value (110)

'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

"steve_m" wrote:

I have a large set of data with many entries. I need to find all instances of
any given value in the first column and then return the maximum and minimum
corresponding value in the second column. For example:

110 25.3
111 6.3
113 7.5
110 11.2
112 21.3
113 11.8
114 30.1
110 2.7

In this example I would like to be able to specify a lookup value, 110 for
example and then have a formula/formulae to retrieve the maximum and minimum
value in the second column, 25.3 and 2.7 in this case.
Thanks in advance

Joel,

I've just read your post correctly your not referring to blanks; which would
give my formula a problem (as they would yours), your referring to all
positive numbers. What combination of postive numbers would cause my formula
to return zero?

Mike

"Joel" wrote:

I fyour numbers are all positive then min won't work because youwill return
0. try this instead

=MIN(SUBSTITUTE(--($A$1:$A$8=C1),0,1E+99)*$B$1:$B$8)

"Mike H" wrote:

Hi

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

Wher C1 is the lookup value (110)

'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

"steve_m" wrote:

I have a large set of data with many entries. I need to find all instances of
any given value in the first column and then return the maximum and minimum
corresponding value in the second column. For example:

110 25.3
111 6.3
113 7.5
110 11.2
112 21.3
113 11.8
114 30.1
110 2.7

In this example I would like to be able to specify a lookup value, 110 for
example and then have a formula/formulae to retrieve the maximum and minimum
value in the second column, 25.3 and 2.7 in this case.
Thanks in advance