Conditional data validation drop-down lists

[quemuenchatocha]

Cordial greetings

I have two concerns and they are as follows.

The first one is, in column A are registered 6 States with their respective cities, the name of each State is in capital letters, now, I need to extract in a separate column (can be for example column L) only the names corresponding to the 6 States, use the function FILTER, ISBLANK and EXACT(UPPER), but I can not get the expected results. Is it possible to obtain this list of only the States in a separate column?

The second query has to do with the longitude and latitude values for each of the cities. For cells H3 and H7 are the values of the States while in cells H4 and H8 are the cities for each of the states recorded in a data list.

For example, in cells I4 and J4 I get the data corresponding to the longitude and latitude respectively for the State and city chosen in cells H3 and H4, the problem lies for those cities in different States that have the same name, for example, if I want to do this same calculation, but this time for the city of Greenville in the State of North Carolina (or Texas, Tennessee, South Carolina) in cells I8 and J8, I get the same results obtained for the city of Greenville but in the State of Illinois.

Is there any formula where it is possible to calculate the corresponding values for each of the cities in your respective state?

Libro1
	A	B	C	D	E	F	G	H	I	J	K	L
1	ALABAMA	LATITUDE	LONGITUDE
2	Alexander City	32,95	85,95				Starting Location		LAT	LON		States
3	Anniston	33,58	85,85				State	ILLINOIS				ALABAMA
4	Auburn	32,60	85,50				City	Greenville	38,8833333	89,4		ILLINOIS
5	Birmingham	33,57	86,75									NORTH CAROLINA
6	Decatur	34,62	86,98				Ending Location					SOUTH CAROLINA
7	Dothan	31,32	85,45				State	NORTH CAROLINA				TENNESSEE
8	Florence	34,80	87,67				City	Greenville	38,8833333	89,4		TEXAS
9	Gadsden	34,02	86,00
10	Huntsville	34,70	86,58
11	Mobile	30,68	88,25
12	Mobile Co	30,67	88,25
13	Montgomery	32,38	86,37
14	Selma-Craig AFB	32,33	87,98
15	Talladega	33,45	86,1
16	Tuscaloosa	33,217	87,617
17
18	ILLINOIS	LATITUDE	LONGITUDE
19	Aurora	41,75	88,33
20	Belleville, Scott AFB	38,55	89,85
21	Bloomington	40,48	88,95
22	Carbondale	37,78	89,25
23	Champaign/Urbana	40,03	88,28
24	Chicago, Midway	41,78	87,75
25	Chicago, O'Hare	41,98	87,90
26	Chicago Co	41,88	87,63
27	Danville	40,20	87,60
28	Decatur	39,83	88,87
29	Dixon	41,83	89,48
30	Elgin	42,03	88,27
31	Freeport	42,30	89,62
32	Galesburg	40,93	90,43
33	Greenville	38,88	89,40
34	Joliet	41,52	88,17
35	Kankakee	41,08	87,92
36	La Salle/Peru	41,32	89,10
37	Macomb	40,47	90,67
38	Moline	41,45	90,52
39	Mt Vernon	38,32	88,87
40	Peoria	40,67	89,68
41	Quincy	39,95	91,20
42	Rantoul, Chanute AFB	40,30	88,13
43	Rockford	42,35	89,05
44	Springfield	39,83	89,67
45	Waukegan	42,35	87,88
46
47	NORTH CAROLINA	LATITUDE	LONGITUDE
48	Asheville	35,43	82,53
49	Charlotte	35,22	80,93
50	Durham	35,87	78,78
51	Elizabeth City	36,27	76,18
52	Fayetteville, Pope AFB	35,17	79,02
53	Goldsboro,Seymour-Johnson	35,33	77,97
54	Greensboro	36,08	79,95
55	Greenville	35,62	77,42
56	Henderson	36,37	78,42
57	Hickory	35,75	81,38
58	Jacksonville	34,83	77,62
59	Lumberton	34,62	79,07
60	New Bern	35,08	77,05
61	Raleigh/Durham	35,87	78,78
62	Rocky Mount	35,97	77,80
63	Wilmington	34,27	77,92
64	Winston-Salem	36,13	80,22
65
66	SOUTH CAROLINA	LATITUDE	LONGITUDE
67	Anderson	34,50	82,72
68	Charleston AFB	32,90	80,03
69	Charleston Co	32,90	79,97
70	Columbia	33,95	81,12
71	Florence	34,18	79,72
72	Georgetown	33,38	79,28
73	Greenville	34,90	82,22
74	Greenwood	34,17	82,12
75	Orangeburg	33,50	80,87
76	Rock Hill	34,98	80,97
77	Spartanburg	34,97	82,00
78	Sumter, Shaw AFB	33,90	80,37
79
80	TENNESSEE	LATITUDE	LONGITUDE
81	Athens	35,43	84,58
82	Bristol-Tri City	36,48	82,40
83	Chattanooga	35,03	85,20
84	Clarksville	36,55	87,37
85	Columbia	35,63	87,03
86	Dyersburg	36,02	89,40
87	Greenville	36,07	82,83
88	Jackson	35,60	88,92
89	Knoxville	35,82	83,98
90	Memphis	35,05	90,00
91	Murfreesboro	34,92	86,47
92	Nashville	36,12	86,68
93	Tullahoma	35,38	86,08
94
95	TEXAS	LATITUDE	LONGITUDE
96	Abilene	32,42	99,68
97	Alice	27,73	98,03
98	Amarillo	35,23	100,70
99	Austin	30,30	97,70
100	Bay City	29,00	95,97
101	Beaumont	29,95	94,02
102	Beeville	28,37	97,67
103	Big Spring	32,30	101,45
104	Brownsville	25,90	97,43
105	Brownwood	31,80	98,95
106	Bryan	30,67	96,55
107	Corpus Christi	27,77	97,50
108	Corsicana	32,08	96,47
109	Dallas	32,85	96,85
110	Del Rio, Laughlin AFB	29,37	100,78
111	Denton	33,20	97,10
112	Eagle Pass	28,87	100,53
113	El Paso	31,80	106,40
114	Fort Worth	32,83	97,05
115	Galveston	29,30	94,80
116	Greenville	33,07	96,05
117	Harlingen	26,23	97,65
118	Houston	29,97	95,35
119	Houston Co	29,98	95,37
120	Huntsville	30,72	95,55
121	Killeen, Robert Gray AAF	31,08	97,68
122	Lamesa	32,70	101,93
123	Laredo AFB	27,53	99,45
124	Longview	32,47	94,73
125	Lubbock	33,65	101,82
126	Lufkin	31,42	94,80
127	Mcallen	26,20	98,22
128	Midland	31,95	102,18
129	Mineral Wells	32,78	98,07
130	Palestine Co	31,78	95,63
131	Pampa	35,53	100,98
132	Pecos	31,42	103,50
133	Plainview	34,18	101,70
134	Port Arthur	29,95	94,02
135	Goodfellow AFB	31,43	100,40
136	San Antonio	29,53	98,47
137	Sherman, Perrin AFB	33,72	96,67
138	Snyder	32,72	100,92
139	Temple	31,10	97,35
140	Tyler	32,35	95,27
141	Vernon	34,17	99,30
142	Victoria	28,85	96,92
143	Waco	31,62	97,22
144	Wichita Falls	33,97	98,48
Hoja1

Cell Formulas
Range	Formula
I4	I4	=INDEX(B:B,MATCH($H$4,A:A,0))
J4	J4	=INDEX(C:C,MATCH($H$4,A:A,0))
I8	I8	=INDEX(B:B,MATCH($H$8,A:A,0))
J8	J8	=INDEX(C:C,MATCH($H$8,A:A,0))

Named Ranges
Name	Refers To	Cells
ALABAMA	=Hoja1!$A$2:$A$16	I8:J8, I4:J4
ILLINOIS	=Hoja1!$A$19:$A$45	I8:J8, I4:J4
NORTH_CAROLINA	=Hoja1!$A$48:$A$64	I8:J8, I4:J4
SOUTH_CAROLINA	=Hoja1!$A$67:$A$78	I8:J8, I4:J4
TENNESSEE	=Hoja1!$A$81:$A$93	I8:J8, I4:J4
TEXAS	=Hoja1!$A$96:$A$144	I8:J8, I4:J4

Cells with Data Validation
Cell	Allow	Criteria
H3	List	=$L$3:$L$8
H4	List	=INDIRECTO(SUSTITUIR($H$3;" ";"_"))
H7	List	=$L$3:$L$8
H8	List	=INDIRECTO(SUSTITUIR($H$7;" ";"_"))

Thank you very much for your attention and help with any hints on a formula that can be used to obtain the results!

 

[Marcelo Branco]

First question - list the states

Try in L3
=IFERROR(INDEX(A$1:A$1000,AGGREGATE(15,6,(ROW(A$1:A$1000)-ROW(A$1)+1)/(EXACT(A$1:A$1000,UPPER(A$1:A$1000))*(A$1:A$1000<>"")),ROWS(L$3:L3))),"")
copy down

M.

 

[Marcelo Branco]

Second question
I4
=INDEX(INDEX(B:B,MATCH($H3,$A:$A,0)):B$1000,MATCH($H4,INDEX($A:$A,MATCH($H3,$A:$A,0)):$A$1000,0))
drag to J4

Then
select I4:J4
copy (Ctrl+C)
select I8
paste (Ctrl+V)

Hope this helps

M.

 

[Marcelo Branco]

A more robust formula
I4
=IF(ISNA(MATCH($H4,INDIRECT(SUBSTITUTE($H3," ","_")),0)),"",INDEX(INDEX(B:B,MATCH($H3,$A:$A,0)):B$1000,MATCH($H4,INDEX($A:$A,MATCH($H3,$A:$A,0)):$A$1000,0)))

drag to J4
Select I4:J4
Copy
Select I8
Paste

M.

 

[quemuenchatocha]

A more robust formula

Dear @Marcelo Branco Thank you for your collaboration, for the first question, the formula is a little confusing to me but in the development I will understand it a little better. Regarding the second question, your solutions work very well, both are really useful, regards hug!

 

[Marcelo Branco]

You're welcome. Thanks for the feedback.

M.