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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]

Country​

[/td][td]Area[/td][td]Building permits[/td][td]Capital[/td][td]Country/region[/td][td]Abbreviation[/td][td]Name[/td][td]Population[/td][td]Persons per household[/td][td]Time zone(s)[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Alabama[/td][td]

135,765​

[/td][td][/td][td]Montgomery[/td][td][/td][td]US-AL[/td][td]Alabama[/td][td]

4,874,747​

[/td][td]

2.55​

[/td][td]Central Time Zone[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]California[/td][td][/td][td]

102,350​

[/td][td][/td][td][/td][td]US-CA[/td][td]California[/td][td]

39,536,653​

[/td][td][/td][td]Pacific Time Zone[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Florida[/td][td]

170,304​

[/td][td][/td][td]Tallahassee[/td][td]United States[/td][td][/td][td]Florida[/td][td]

21,670,000​

[/td][td]

2.63​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]Hawaii[/td][td]

28,311​

[/td][td]

3,369​

[/td][td][/td][td][/td][td]US-HI[/td][td][/td][td]

1,427,538​

[/td][td][/td][td]Hawaii-Aleutian Time Zone[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]Illinois[/td][td]

149,998​

[/td][td][/td][td][/td][td][/td][td][/td][td]Illinois[/td][td][/td][td]

2.63​

[/td][td]Central Time Zone[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]Indiana[/td][td][/td][td]

18,713​

[/td][td]Indianapolis[/td][td]United States[/td][td][/td][td][/td][td]

6,666,818​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]Nevada[/td][td]

286,367​

[/td][td]

17,952​

[/td][td]Carson City[/td][td]United States[/td][td]US-NV[/td][td]Nevada[/td][td][/td][td]

2.72​

[/td][td]Mountain Time Zone, Pacific Time Zone[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]New Jersey[/td][td]

22,608​

[/td][td]

26,793​

[/td][td][/td][td]United States[/td][td]US-NJ[/td][td][/td][td]

9,005,644​

[/td][td]

2.73​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]New York[/td][td][/td][td][/td][td]Albany[/td][td][/td][td]US-NY[/td][td]New York[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]Oregon[/td][td]

255,026​

[/td][td]

19,586​

[/td][td][/td][td]United States[/td][td]US-OR[/td][td]Oregon[/td][td]

4,142,776​

[/td][td]

2.51​

[/td][td]Mountain Time Zone, Pacific Time Zone[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]Texas[/td][td][/td][td][/td][td]Austin[/td][td][/td][td]US-TX[/td][td][/td][td]

28,304,596​

[/td][td]

2.84​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]Washington[/td][td]

184,827​

[/td][td]

44,077​

[/td][td]Olympia[/td][td][/td][td][/td][td]Washington[/td][td]

7,405,743​

[/td][td]

2.56​

[/td][td]Pacific Time Zone[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td][/td][td][/td][td]

​

[/td][td]

Florida​

[/td][td]<==Choose Country from the list[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td][/td][td][/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td][/td][td][/td][td]

Area​

[/td][td]

170,304​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

19​

[/td][td][/td][td][/td][td]

Capital​

[/td][td]

Tallahassee​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

20​

[/td][td][/td][td][/td][td]

Country/region​

[/td][td]

United States​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

21​

[/td][td][/td][td][/td][td]

Name​

[/td][td]

Florida​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

22​

[/td][td][/td][td][/td][td]

Population​

[/td][td]

21,670,000​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

23​

[/td][td][/td][td][/td][td]

Persons per household​

[/td][td]

2.63​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

24​

[/td][td][/td][td][/td][td]

Time zone(s)​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Data [/td][/tr][/table]

 

[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?