Display a list only with the values by country

[Omer_K]

Hello everyone,
I asked a question in the past, but there are some changes here:
The table above has data for each country - as you can see - there are a number of missing data.
Cell D16 has a Data Validation list of all countries
I would like to know that if I select a particular country from the list, I will only see the full data,
For example, if I choose the state of Florida, I will only see a list of complete data in the table below.
But anyway (even if it's empty) I want to see the three columns of:
Population, Persons per household, and Time zone (s)


Hope i was clear,


Thank you

Omer


Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	Country​	Area	Building permits	Capital	Country/region	Abbreviation	Name	Population	Persons per household	Time zone(s)
2​	Alabama	135,765​		Montgomery		US-AL	Alabama	4,874,747​	2.55​	Central Time Zone
3​	California		102,350​			US-CA	California	39,536,653​		Pacific Time Zone
4​	Florida	170,304​		Tallahassee	United States		Florida	21,670,000​	2.63​
5​	Hawaii	28,311​	3,369​			US-HI		1,427,538​		Hawaii-Aleutian Time Zone
6​	Illinois	149,998​					Illinois		2.63​	Central Time Zone
7​	Indiana		18,713​	Indianapolis	United States			6,666,818​
8​	Nevada	286,367​	17,952​	Carson City	United States	US-NV	Nevada		2.72​	Mountain Time Zone, Pacific Time Zone
9​	New Jersey	22,608​	26,793​		United States	US-NJ		9,005,644​	2.73​
10​	New York			Albany		US-NY	New York
11​	Oregon	255,026​	19,586​		United States	US-OR	Oregon	4,142,776​	2.51​	Mountain Time Zone, Pacific Time Zone
12​	Texas			Austin		US-TX		28,304,596​	2.84​
13​	Washington	184,827​	44,077​	Olympia			Washington	7,405,743​	2.56​	Pacific Time Zone
14​
15​
16​			​	Florida​	<==Choose Country from the list
17​			​
18​			Area​	170,304​
19​			Capital​	Tallahassee​
20​			Country/region​	United States​
21​			Name​	Florida​
22​			Population​	21,670,000​
23​			Persons per household​	2.63​
24​			Time zone(s)​	​

Sheet: Data

 

[Aladin Akyurek]

Dear Aladin - you are the best!!
if i wuold like No matter what, at the bottom of the list in range C18:.. always get the three last columns: Population,Persons per household,Time zone(s) Although one may be empty..

In C18 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$1:$J$1,SMALL(IF(ISNUMBER(MATCH($B$1:$J$1,{"Population","Persons per household","Time zone(s)"},0))+(INDEX($B$2:$J$13,MATCH($D$16,$A$2:$A$13,0),0)<>""),COLUMN($B$1:$J$1)-COLUMN($B$1)+1),ROWS($A$18:A18))),"")

Is this what you have in mind?