Match and select

[apgmin]

CENTER CODE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	STATE	CODE		MAHARASHTRA		MADHYA PRADESH		KARNATAKA		GUJARAT			STATE	STATE CODE	DISTRICT	DISTRCIT CODE
2	Gujarat	GJ		Ahmednagar	01	Agar-Malwa	01	Bagalkot	01	Ahmedabad	01		Madhya Pradesh	MP	Formula 1	Formula 2
3	Karnataka	KA		Akola	02	Alirajpur	02	Ballari	02	Amreli	02		Karnataka	KA	Formula 1	Formula 2
4	Madhya Pradesh	MP		Amravati	03	Anuppur	03	Belagavi	03	Anand	03				Formula 1	Formula 2
5	Maharashtra	MH		Beed	04	Ashoknagar	04	Bengaluru Rural	04	Aravalli	04				Formula 1	Formula 2
6				Bhandara	05	Balaghat	05	Bengaluru Urban	05	Banaskantha	05				Formula 1	Formula 2
7				Buldhana	06	Barwani	06	Bidar	06	Bharuch	06				Formula 1	Formula 2
8				Chandrapur	07	Betul	07	Chamarajanagar	07	Bhavnagar	07				Formula 1	Formula 2
9				Chhatrapati Sambhajinagar	08	Bhind	08	Chikballapur	08	Botad	08				Formula 1	Formula 2
10				Dharashiv	09	Bhopal	09	Chikkamagaluru	09	Chhota Udepur	09				Formula 1	Formula 2
CENTER

Cell Formulas
Range	Formula
N2:N10	N2	=IF(M2>0,VLOOKUP(M2,$A$1:$B$10,2,FALSE),"")

Cells with Data Validation
Cell	Allow	Criteria
M2:M10	List	=$A$2:$A$10

In Cells M2:M10, we can select only the state names from the list in A1:A10

Corresponding to the state selected the code from b2 to b10 comes up in N2:N10, So Madhya Pradesh in M2 shows MP in N2 and karnataks in M3 shows KA in N3

I need help in

Formula 1: It should make me select only the list of district based on the state selected earlier. Like say in M2, If I select the state names as Madhya Pradesh then I can select the district names listed only in cells F2 to F10 AND If I select the state names as Maharashtra then I can select the district names listed only in cells D2 to D10

Formula 2 should match the district selected in O2 and give the code for that district,

So Say In M2 for Madhya Pradesh if I select the district Ashok Nagar in O2 it should show 04 and If I select Bhind it should show 08

Similarly In M2 for Maharashtra if I select the district Akola in O2, it should show 02 and If I select Beed it should show 04

 

[Cubist]

Can only help with Formula 1. Give your options named ranges Uppercase with 3 letters. MAH (D2:D10), MAD (F2:F10), KAR(H2:H10), GUJ (J2:J10). The select all the cells in column O, cell validation -> List -> Insert the INDRECT() formula below. If you stack your selection ranges into 2 columns then it's easy for formula 2, but the way it is structured now, I don't know how to do it in 2013.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	STATE	CODE		MAHARASHTRA		MADHYA PRADESH		KARNATAKA		GUJARAT			STATE	STATE CODE	DISTRICT	DISTRICT CODE
2	Gujarat	GJ		Ahmednagar	1	Agar-Malwa	1	Bagalkot	1	Ahmedabad	1		Madhya Pradesh	MP	Barwani
3	Karnataka	KA		Akola	2	Alirajpur	2	Ballari	2	Amreli	2		Karnataka	KA	Bengaluru Rural
4	Madhya Pradesh	MP		Amravati	3	Anuppur	3	Belagavi	3	Anand	3
5	Maharashtra	MH		Beed	4	Ashoknagar	4	Bengaluru Rural	4	Aravalli	4
6				Bhandara	5	Balaghat	5	Bengaluru Urban	5	Banaskantha	5
7				Buldhana	6	Barwani	6	Bidar	6	Bharuch	6
8				Chandrapur	7	Betul	7	Chamarajanagar	7	Bhavnagar	7
9				Chhatrapati Sambhajinagar	8	Bhind	8	Chikballapur	8	Botad	8
10				Dharashiv	9	Bhopal	9	Chikkamagaluru	9	Chhota Udepur	9
Sheet5

Cell Formulas
Range	Formula
N2:N10	N2	=IF(M2>0,VLOOKUP(M2,$A$1:$B$10,2,FALSE),"")

Cells with Data Validation
Cell	Allow	Criteria
O2:O10	List	=INDIRECT(UPPER(LEFT(M2,3)))