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!