Finding the largest match

Hello!

I'm trying to set up a function to basically do two tasks at the same time,
and I'm not sure if its possible without getting into VisualBasic... Here's
what I want to do:

1. Search a column range for values matching the one I specify.
2. Find the largest value in a different column in the matching rows from
the search.

For example, in the table below I want to find the largest value in ColB
that has a 3 in ColA:

Col A Col B
Row1 12 20
Row2 3 10
Row3 9 80
Row4 3 50
Row5 5 20
Row6 3 20

The function would first identify rows 2, 4 & 6, then identify 50 as the
largest value in ColB in those rows.

Thanks for any help!

Trt this array formula

=MAX(IF(A1:A6=3,B1:B6))

This is an array formula which must be entered with 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 ranges
'then you must re-enter as An array


Mike

"XJSquared" wrote:

Hello!

I'm trying to set up a function to basically do two tasks at the same time,
and I'm not sure if its possible without getting into VisualBasic... Here's
what I want to do:

1. Search a column range for values matching the one I specify.
2. Find the largest value in a different column in the matching rows from
the search.

For example, in the table below I want to find the largest value in ColB
that has a 3 in ColA:

Col A Col B
Row1 12 20
Row2 3 10
Row3 9 80
Row4 3 50
Row5 5 20
Row6 3 20

The function would first identify rows 2, 4 & 6, then identify 50 as the
largest value in ColB in those rows.

Thanks for any help!

Hi,

Here is a non-array approach:

=SUMPRODUCT(MAX((A1:A6=H1)*B1:B6))

Where the value you are checking is in H1 or you can enter it directly in
the formula.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"XJSquared" wrote:

Hello!

I'm trying to set up a function to basically do two tasks at the same time,
and I'm not sure if its possible without getting into VisualBasic... Here's
what I want to do:

1. Search a column range for values matching the one I specify.
2. Find the largest value in a different column in the matching rows from
the search.

For example, in the table below I want to find the largest value in ColB
that has a 3 in ColA:

Col A Col B
Row1 12 20
Row2 3 10
Row3 9 80
Row4 3 50
Row5 5 20
Row6 3 20

The function would first identify rows 2, 4 & 6, then identify 50 as the
largest value in ColB in those rows.

Thanks for any help!

Hi,

You may also try this

=MAX(INDEX(($A$1:$A$6=A9)*(B1:B6),,1))

A9 holds 3

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"XJSquared" wrote in message
...
Hello!

I'm trying to set up a function to basically do two tasks at the same
time,
and I'm not sure if its possible without getting into VisualBasic...
Here's
what I want to do:

1. Search a column range for values matching the one I specify.
2. Find the largest value in a different column in the matching rows from
the search.

For example, in the table below I want to find the largest value in ColB
that has a 3 in ColA:

Col A Col B
Row1 12 20
Row2 3 10
Row3 9 80
Row4 3 50
Row5 5 20
Row6 3 20

The function would first identify rows 2, 4 & 6, then identify 50 as the
largest value in ColB in those rows.

Thanks for any help!

Shane Devenshire wrote...
Here is a non-array approach:

=SUMPRODUCT(MAX((A1:A6=H1)*B1:B6))
....

Semipicky: this fails if there are negative values in col B, in which
case the largest value could be negative; also fails if any cell in
col B is nonnumeric text, in which case this formula would return
#VALUE!.

There are times when array formulas ARE the most robust of various
alternatives. This is one of those times.

Harlan, When you are being semipicky, is it not incumbent on you to provide
your solution?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Harlan Grove" wrote in message
...
Shane Devenshire wrote...
Here is a non-array approach:

=SUMPRODUCT(MAX((A1:A6=H1)*B1:B6))
...

Semipicky: this fails if there are negative values in col B, in which
case the largest value could be negative; also fails if any cell in
col B is nonnumeric text, in which case this formula would return
#VALUE!.

There are times when array formulas ARE the most robust of various
alternatives. This is one of those times.

I think Harlan was referring to Mike's posted array formula as the solution.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...
Harlan, When you are being semipicky, is it not incumbent on you to
provide your solution?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Harlan Grove" wrote in message
...
Shane Devenshire wrote...
Here is a non-array approach:

=SUMPRODUCT(MAX((A1:A6=H1)*B1:B6))
...

Semipicky: this fails if there are negative values in col B, in which
case the largest value could be negative; also fails if any cell in
col B is nonnumeric text, in which case this formula would return
#VALUE!.

There are times when array formulas ARE the most robust of various
alternatives. This is one of those times.

Then why didn't he post this CSE?

=MAX(IF(($A$1:$A$16=$D$1)*($B$1:$B$160),$B$1:$B$1 6))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
I think Harlan was referring to Mike's posted array formula as the
solution.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...
Harlan, When you are being semipicky, is it not incumbent on you to
provide your solution?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Harlan Grove" wrote in message
...
Shane Devenshire wrote...
Here is a non-array approach:

=SUMPRODUCT(MAX((A1:A6=H1)*B1:B6))
...

Semipicky: this fails if there are negative values in col B, in which
case the largest value could be negative; also fails if any cell in
col B is nonnumeric text, in which case this formula would return
#VALUE!.

There are times when array formulas ARE the most robust of various
alternatives. This is one of those times.

I'm not sure I understand your response. The array formula Mike posted
produces correct results under the two conditions Harlan posted (all
negative values and/or text in Column B) where as Shane's non-array formula
fails. Since Shane offered his formula as an alternative to Mike's, all I
think Harlan was doing was pointing out that Mike's array formula was
superior to Shane's non-array alternative because it didn't fail under the
those two conditions.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...
Then why didn't he post this CSE?

=MAX(IF(($A$1:$A$16=$D$1)*($B$1:$B$160),$B$1:$B$1 6))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
I think Harlan was referring to Mike's posted array formula as the
solution.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...
Harlan, When you are being semipicky, is it not incumbent on you to
provide your solution?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Harlan Grove" wrote in message
...
Shane Devenshire wrote...
Here is a non-array approach:

=SUMPRODUCT(MAX((A1:A6=H1)*B1:B6))
...

Semipicky: this fails if there are negative values in col B, in which
case the largest value could be negative; also fails if any cell in
col B is nonnumeric text, in which case this formula would return
#VALUE!.

There are times when array formulas ARE the most robust of various
alternatives. This is one of those times.

I went back and tested and found that Mike's works, AS IS.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
I'm not sure I understand your response. The array formula Mike posted
produces correct results under the two conditions Harlan posted (all
negative values and/or text in Column B) where as Shane's non-array
formula fails. Since Shane offered his formula as an alternative to
Mike's, all I think Harlan was doing was pointing out that Mike's array
formula was superior to Shane's non-array alternative because it didn't
fail under the those two conditions.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...
Then why didn't he post this CSE?

=MAX(IF(($A$1:$A$16=$D$1)*($B$1:$B$160),$B$1:$B$1 6))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
I think Harlan was referring to Mike's posted array formula as the
solution.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...
Harlan, When you are being semipicky, is it not incumbent on you to
provide your solution?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Harlan Grove" wrote in message
...
Shane Devenshire wrote...
Here is a non-array approach:

=SUMPRODUCT(MAX((A1:A6=H1)*B1:B6))
...

Semipicky: this fails if there are negative values in col B, in which
case the largest value could be negative; also fails if any cell in
col B is nonnumeric text, in which case this formula would return
#VALUE!.

There are times when array formulas ARE the most robust of various
alternatives. This is one of those times.