Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
CWatsonJr
 
Posts: n/a
Default Create List based on cell data


I have been trying to do this various ways and can just not get it
right.

I have a table that has multiple cities and populations and square
miles:
A B C
1 city 1 sq mile 1 pop 1
2 city 2 sq mile 2 pop 2
3 city 3 sq mile 3 pop 3
4 city 4 sq mile 4 pop 4
5 city 5 sq mile 5 pop 5

I know how to order the list by (smallest to largest) number:

Square Miles Population
10 =large(b1:b5,5) =large(c1:c5,5)
11 =large(b1:b5,4) =large(c1:c5,4)

etc..

I would prefer to be able to display the name of the city (instead of
the squre miles or population) that matches. I have tried some if...
statements but I just can not get it right.

Any ideas?

Cliff Watson


--
CWatsonJr
------------------------------------------------------------------------
CWatsonJr's Profile: http://www.excelforum.com/member.php...fo&userid=6603
View this thread: http://www.excelforum.com/showthread...hreadid=467973

  #2   Report Post  
Dave O
 
Posts: n/a
Default

I got a result by nesting the LARGE() function into an INDEX / MATCH
formula, like this:
=INDEX(A1:B5,MATCH(LARGE(B1:B5,5),B1:B5,0),1)

  #3   Report Post  
John Moore
 
Posts: n/a
Default

This may help. I have added some numbers to show better what I think you are
referring to. I have added another column onto the end of your data, it's a
simple copy fo the cities in column A.
A B C D
1 City sq mile pop City
2 city 1 10 50 city 1
3 city 2 20 40 city 2
4 city 3 30 30 city 3
5 city 4 40 20 city 4
6 city 5 50 10 city 5

Below is how I think you wnat to see the data, cell B2 contains a formula
=MAX(B2:B6), cells A9 and C9 contain VLOOKUPS based on the data in cells B2
thru c6.
formula in C6 looks like =VLOOKUP($B9,$B$1:$D$6,2,0)

A B C
8 City sq mile pop
9 city 5 50 10


"CWatsonJr" wrote:


I have been trying to do this various ways and can just not get it
right.

I have a table that has multiple cities and populations and square
miles:
A B C
1 city 1 sq mile 1 pop 1
2 city 2 sq mile 2 pop 2
3 city 3 sq mile 3 pop 3
4 city 4 sq mile 4 pop 4
5 city 5 sq mile 5 pop 5

I know how to order the list by (smallest to largest) number:

Square Miles Population
10 =large(b1:b5,5) =large(c1:c5,5)
11 =large(b1:b5,4) =large(c1:c5,4)

etc..

I would prefer to be able to display the name of the city (instead of
the squre miles or population) that matches. I have tried some if...
statements but I just can not get it right.

Any ideas?

Cliff Watson


--
CWatsonJr
------------------------------------------------------------------------
CWatsonJr's Profile: http://www.excelforum.com/member.php...fo&userid=6603
View this thread: http://www.excelforum.com/showthread...hreadid=467973


  #4   Report Post  
CWatsonJr
 
Posts: n/a
Default


Thank you everyone for your replies!!!

Dave O. I found your formula the easiest to apply to my situation.

Thanks again everyone!!

Cliff Watson


--
CWatsonJr
------------------------------------------------------------------------
CWatsonJr's Profile: http://www.excelforum.com/member.php...fo&userid=6603
View this thread: http://www.excelforum.com/showthread...hreadid=467973

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
named range, data validation: list non-selected items, and new added items KR Excel Discussion (Misc queries) 1 June 24th 05 06:21 AM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 10:43 PM
format based on data from another cell Sanna R. New Users to Excel 1 April 19th 05 07:34 PM
How to filter and list data based on different data. Defoes Right Boot Excel Worksheet Functions 3 April 13th 05 05:03 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 12:53 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"