Look Up

  • Thread starter Thread starter Jon
  • Start date Start date
J

Jon

How can I generate a list of the states with the most
people in my databse? I have the spreadsheet:
A B
Alabama 134
Alaska 26
Arizona 499
Arkansas 58

I want to list the top 5 states... Teh formula must look
at the list with the number of people and the state with
the highest total on a line. Below that line I want the
formula to put the state with the second highest people
and so on until the top five states are listed.

Any suggestions?
 
Hi Jon
just a simple sort is not an option?. O.K. then some formulas:
put the following in C1:for the highest total
=INDEX($A$1:$A$55,MATCH(LARGE($B$1:$B$55,1),$B$1:$B$55,0))
for the highest total-1
=INDEX($A$1:$A$55,MATCH(LARGE($B$1:$B$55,2),$B$1:$B$55,0))
If you start in C1 replace the first formula with
=INDEX($A$1:$A$55,MATCH(LARGE($B$1:$B$55,ROW()),$B$1:$B$55,0))
and copy down

Note: You will get problems if two states within your top-list have the
same number of people. The above formula will return only the name of
the first state for both occurences. If this could happen in your data
have a look at
http://www.cpearson.com/excel/rank.htm

HTH
Frank
 
Jon,

=INDEX($A$1:$A$50,MATCH(LARGE($B$1:$B$50,ROW(A1)),$B$1:$B$50,0))

and copy down for 4 cel,ls

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thank you!

I will try it.

Jon
-----Original Message-----
Jon,

=INDEX($A$1:$A$50,MATCH(LARGE($B$1:$B$50,ROW (A1)),$B$1:$B$50,0))

and copy down for 4 cel,ls

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)




.
Click to expand...
 
Thank you!!!

-----Original Message-----
Hi Jon
just a simple sort is not an option?. O.K. then some formulas:
put the following in C1:for the highest total
=INDEX($A$1:$A$55,MATCH(LARGE($B$1:$B$55,1),$B$1:$B$55,0))
for the highest total-1
=INDEX($A$1:$A$55,MATCH(LARGE($B$1:$B$55,2),$B$1:$B$55,0))
If you start in C1 replace the first formula with
=INDEX($A$1:$A$55,MATCH(LARGE($B$1:$B$55,ROW ()),$B$1:$B$55,0))
and copy down

Note: You will get problems if two states within your top- list have the
same number of people. The above formula will return only the name of
the first state for both occurences. If this could happen in your data
have a look at
http://www.cpearson.com/excel/rank.htm

HTH
Frank





.
Click to expand...
 
I'm sit in front of a italian version.
You must reply the column number at the left of your matrix.
The function "Grande" accept a range and an index for 1st or 2nd and so
on position. Once you've got this number you can use the CERCA.VERT
function to find out this value in the first column of the new matrix -
that one with dwells' number on the left. In the Cerca.VERT specify
which items (i.e. which column) you want it returns.

A B
Alabama 134
Alaska 26
Arizona 499
Arkansas 58

|
V

A B
134 Alabama
26 Alaska
499 Arizona
58 Arkansas

then
=CERCA.VERT(GRANDE(A1:A4;index));A1:B4;column;FALSO)

where you must substitute "index" with 1 for the 1st, with 2 for the 2nd
etc (in your case it will go from 1 to 5)
and ";" with "," for english version
and "FALSO" with "FALSE"
and "column" with the index value of the the column you desire (2 for
state's name in your example)

The last effort is translating italians commands in the english ones ;)

HTH
Ciao Alex
 
Back
Top