Finding the largest match

[XJSquared]

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!

 

[Mike H]

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

 

[Shane Devenshire]

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.

 

[Ashish Mathur]

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

 

[Harlan Grove]

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.

 

[Don Guillett]

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

 

[Rick Rothstein]

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

 

[Don Guillett]

Then why didn't he post this CSE?

=MAX(IF(($A$1:$A$16=$D$1)*($B$1:$B$16>0),$B$1:$B$16))

 

[Rick Rothstein]

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.

 

[Don Guillett]

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