One way:

=MAX(IF(Sheet2!A1:A10="hi",Sheet2!B1:B10))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

You could even check to see if there was any data that matched:

=IF(COUNTIF(Sheet2!A1:A10,"hi")=0,"No matches",
MAX(IF(Sheet2!A1:A10="hi",Sheet2!B1:B10)))

(one cell, and still an array formula)



rmellison wrote:

I have a list of parameter names and a corresponding list of values; there
may be ten or so of each of the parameters within the list. I have been using
SUBTOTAL(4,range) to find the maximum of a parameter when filtered using
autofilter, and up until now this has been fine.

Now I find myself needing to do a similar thing for an unfiltered list which
is on another worksheet. I want to be able to find the maximum of all the
values corresponding to all entries of a particular parameter within a list.
I suspect that the way to do it is using VLOOKUP or INDEX/MATCH to find an
array of the numbers I want, and then finding the maximum of that array, but
so far I haven't cracked it....

Any suggestions gladly appreciated...

--

Dave Peterson