Associating highest value with name

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
 
Biff:

Perfect! Thanks, man!

Biff said:
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
Click to expand...
 
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?
 
=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.
 
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
 
Back
Top