How do I find the highest number(s) in a column?

B

BigBuck98

I have a datebase containing 2 columns. In one column I have 105 individuals
names in the other column is a number associated with each name. I would
like to search the column with numbers and find the highest number and the
person(s) name associated with that number and then place the name(s) and
number in
another cell. There could be more than one name with the highest number.
Example of names and numbers appear below.

I have been using the following formula, but I find there are times when
more than two individuals have the same number and then it only print the
first name that it comes to.

In one (i.e. C1) put this formula:
=INDEX(A1:A3,MATCH(MAX(B1:B3),B1:B3,0))

In C2 put this formula:
=MAX(B1:B3)

Is there a way to do this?
Thanks in advance,
Gordon

Smith, George 230
Henry, Bill 210
Thomas, Frank 200
Doe, Richard 230
 
T

T. Valko

One way:

Enter this formula in C1 as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=IF(ROWS($1:1)<=COUNTIF(B$1:B$4,MAX(B$1:B$4)),INDEX(A$1:A$4,SMALL(IF(B$1:B$4=MAX(B$1:B$4),ROW(A$1:A$4)-MIN(ROW(A$1:A$4))+1),ROWS($1:1))),"")

Enter this formula in D1:

=IF(C1="","",MAX(B$1:B$4))

Then, select both C1 and D1 and copy down until you get blanks.

Biff
 
B

BigBuck98

Hi Biff,
I must be doing something wrong, when I put the formula in C1 and use the
key combination of CTRL,SHIFT,ENTER I get a message that states "Array
formulas are not valid in merged cells."
Also do I enter the formula in D1 as an array?

Gordon
 
T

T. Valko

I get a message that states "Array formulas are not valid in merged cells."

Unmerge the cells! Are you expecting to get all the results in a single
cell? It won't work like that.
do I enter the formula in D1 as an array?
Click to expand...

No, it's a normally entered formula.

Biff
 
B

BigBuck98

Biff,

Thanks a lot I got it to work

Gordon


T. Valko said:
Unmerge the cells! Are you expecting to get all the results in a single
cell? It won't work like that.


No, it's a normally entered formula.

Biff
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find team name with highest winning % over multiple years(worksheets) 0
How do I find the highest of the most repeating values in a list ofnumbers ie. the max of the modes 2
having excel create a new column in a highest to lowest format 4
How do I find the second highest value. 3
Find max with a lookup 2
How do I use "column" function to get the column number of the firstcell matching criteria? 2
How do I find the largest in a column 5
Finding the type with the highest overall value. 2

Top