Associating highest value with name

[Guest]

I am trying to write a formula (no luck so far) that will find the highest
value in a list of numbers and then associate it with an office name in
column A. The problem I'm having is that the totals are way over in column
AQ. I then want to transfer the office name to one cell and the value to
another like OFFICE NAME: and then AMOUNT: (and paste the values into a blank
cell next to it). Anyone?

 

[Biff]

Hi!

Try this:

=INDEX(A1:A10,MATCH(MAX(AQ1:AQ10),AQ1:AQ10,0))

Note: if there are multiple instances of MAX the formula will match the
first instance.

Biff

 

[Guest]

Biff:

Perfect! Thanks, man!

Hi!

Try this:

=INDEX(A1:A10,MATCH(MAX(AQ1:AQ10),AQ1:AQ10,0))

Note: if there are multiple instances of MAX the formula will match the
first instance.

Biff

 

[Guest]

Oh...one other thing: what about the LOWEST or LEAST contributor in the
column? How do I sort that out? Also, I don't want to count cells that have a
zero value. Is there a way?

 

[Guest]

=INDEX(A1:A10, MATCH(MIN(IF(AQ1:AQ10,AQ1:AQ10,"")),AQ1:AQ10,0))

Commit with Control+Shift+Enter after typing/pasting it into your formula bar.

 

[Biff]

Here's a non-array alternative as long as none of the values are negative:

=INDEX(A1:A10,MATCH(SMALL(AQ1:AQ10,COUNTIF(AQ1:AQ10,0)+1),AQ1:AQ10,0))

Biff