Hi,

I am trying to get a max age from a range of cells, however the result is
depending on the value in another cell. The range looks like this:

ID BU Age
11348 JPU 44
12479 FKG 46
12487 LPS 27
14539 LPS 32
17583 FKG 24
39175 LPS 34
45867 JPU 42
74583 FKG 39

I need a formula that will give me the max age in the following way:
FKG 46
JPU 44
LPS 34

Is there a way to do this without using a pivot table?

Many thanks,
MLS

Hi,

Try this ARRAY formula. See below on how to enter it

=MAX(IF(B1:B8="FKG",C1:C8))

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



"MLS" wrote:

Hi,

I am trying to get a max age from a range of cells, however the result is
depending on the value in another cell. The range looks like this:

ID BU Age
11348 JPU 44
12479 FKG 46
12487 LPS 27
14539 LPS 32
17583 FKG 24
39175 LPS 34
45867 JPU 42
74583 FKG 39

I need a formula that will give me the max age in the following way:
FKG 46
JPU 44
LPS 34

Is there a way to do this without using a pivot table?

Many thanks,
MLS

Hi Mike,

When I try it gives me a #VALUE! error. Would you know why?

MLS

"Mike H" wrote:

Hi,

Try this ARRAY formula. See below on how to enter it

=MAX(IF(B1:B8="FKG",C1:C8))

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



"MLS" wrote:

Hi,

I am trying to get a max age from a range of cells, however the result is
depending on the value in another cell. The range looks like this:

ID BU Age
11348 JPU 44
12479 FKG 46
12487 LPS 27
14539 LPS 32
17583 FKG 24
39175 LPS 34
45867 JPU 42
74583 FKG 39

I need a formula that will give me the max age in the following way:
FKG 46
JPU 44
LPS 34

Is there a way to do this without using a pivot table?

Many thanks,
MLS

Hi,

I can't replicate that, please post the exact formula you used.

Mike

"MLS" wrote:

Hi Mike,

When I try it gives me a #VALUE! error. Would you know why?

MLS

"Mike H" wrote:

Hi,

Try this ARRAY formula. See below on how to enter it

=MAX(IF(B1:B8="FKG",C1:C8))

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



"MLS" wrote:

Hi,

I am trying to get a max age from a range of cells, however the result is
depending on the value in another cell. The range looks like this:

ID BU Age
11348 JPU 44
12479 FKG 46
12487 LPS 27
14539 LPS 32
17583 FKG 24
39175 LPS 34
45867 JPU 42
74583 FKG 39

I need a formula that will give me the max age in the following way:
FKG 46
JPU 44
LPS 34

Is there a way to do this without using a pivot table?

Many thanks,
MLS

The formula I used was: =MAX(IF(B2:B9="FKG",C2:C9))

The only change I made from the formula you posted was to not include the
header row (Row 1) and instead include the bottom row (Row 9).

MLS

"Mike H" wrote:

Hi,

I can't replicate that, please post the exact formula you used.

Mike

"MLS" wrote:

Hi Mike,

When I try it gives me a #VALUE! error. Would you know why?

MLS

"Mike H" wrote:

Hi,

Try this ARRAY formula. See below on how to enter it

=MAX(IF(B1:B8="FKG",C1:C8))

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



"MLS" wrote:

Hi,

I am trying to get a max age from a range of cells, however the result is
depending on the value in another cell. The range looks like this:

ID BU Age
11348 JPU 44
12479 FKG 46
12487 LPS 27
14539 LPS 32
17583 FKG 24
39175 LPS 34
45867 JPU 42
74583 FKG 39

I need a formula that will give me the max age in the following way:
FKG 46
JPU 44
LPS 34

Is there a way to do this without using a pivot table?

Many thanks,
MLS

Mike has suggested you an array formula..

An array formula can perform multiple calculations and then return either a
single result or multiple results. Array formulas act on two or more sets of
values known as array arguments. Each array argument must have the same
number of rows and columns. You create array formulas in the same way that
you create other formulas, except you press CTRL+SHIFT+ENTER to enter the
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"MLS" wrote:

The formula I used was: =MAX(IF(B2:B9="FKG",C2:C9))

The only change I made from the formula you posted was to not include the
header row (Row 1) and instead include the bottom row (Row 9).

MLS

"Mike H" wrote:

Hi,

I can't replicate that, please post the exact formula you used.

Mike

"MLS" wrote:

Hi Mike,

When I try it gives me a #VALUE! error. Would you know why?

MLS

"Mike H" wrote:

Hi,

Try this ARRAY formula. See below on how to enter it

=MAX(IF(B1:B8="FKG",C1:C8))

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



"MLS" wrote:

Hi,

I am trying to get a max age from a range of cells, however the result is
depending on the value in another cell. The range looks like this:

ID BU Age
11348 JPU 44
12479 FKG 46
12487 LPS 27
14539 LPS 32
17583 FKG 24
39175 LPS 34
45867 JPU 42
74583 FKG 39

I need a formula that will give me the max age in the following way:
FKG 46
JPU 44
LPS 34

Is there a way to do this without using a pivot table?

Many thanks,
MLS

=SUMPRODUCT(MAX(($B$2:$B$9=E2)*$C$2:$C$9))

Just press ENTER


"MLS" wrote:

Hi,

I am trying to get a max age from a range of cells, however the result is
depending on the value in another cell. The range looks like this:

ID BU Age
11348 JPU 44
12479 FKG 46
12487 LPS 27
14539 LPS 32
17583 FKG 24
39175 LPS 34
45867 JPU 42
74583 FKG 39

I need a formula that will give me the max age in the following way:
FKG 46
JPU 44
LPS 34

Is there a way to do this without using a pivot table?

Many thanks,
MLS

I tried this and it also works so thanks!

"Teethless mama" wrote:

=SUMPRODUCT(MAX(($B$2:$B$9=E2)*$C$2:$C$9))

Just press ENTER


"MLS" wrote:

Hi,

I am trying to get a max age from a range of cells, however the result is
depending on the value in another cell. The range looks like this:

ID BU Age
11348 JPU 44
12479 FKG 46
12487 LPS 27
14539 LPS 32
17583 FKG 24
39175 LPS 34
45867 JPU 42
74583 FKG 39

I need a formula that will give me the max age in the following way:
FKG 46
JPU 44
LPS 34

Is there a way to do this without using a pivot table?

Many thanks,
MLS