Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]() 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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]() 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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
format based on data from another cell | New Users to Excel | |||
How to filter and list data based on different data. | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |