Finding the largest match

  • Thread starter Thread starter XJSquared
  • Start date Start date
X

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!
 
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
 
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.
 
Shane Devenshire said:
Here is a non-array approach:

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

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?
 
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))
 
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.
 
Back
Top