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!