Having issues with index match

[Knockoutpie]

Can anyone help on this? I'm trying to pull the city name and province from an address based off a range on sheet two, but I can't seem to get this to work..
The formulas will go in the City and province columns

Test.xlsx
	A	B	C	D	E	F
1	ADDRESS_ENG	CITY	COUNTRY	STATE	POSTAL_CODE	PROVINCE
2	295 YANGGUANG RD,NANGANG TOWN,KUNSHAN		CN		215300
3	3F-10 BUILDING, ZHONGYUNTAI SCIENCE & INDUSTRIAL PARK, TANGTOU 1ST ROAD SHIYAN TOWN, BAOAN DISTRICT, SHENZHEN, GUANGDONG, CHINA		CN		518108
4	3RD FLOOR, NO. 10 BUILDING, ZHONGYUNTAI SCIENCE IND. PARK, TANGTOU 1ST ROAD SHIYAN TOWN, BAOAN DISTRICT, SHENZHEN, GUANGDONG, CHINA		CN		518108
5	7 FLOOR NO.6 BINLANG ROAD, FUTIAN FREE TRADE ZONE ,SHENZHEN		CN
6	B1-B3 2F,NO.1135 XIANGYANG ROAD,MINHANGDISTRICT, SHANGHAI 201108,CHINA		CN		201108
7	B1-B3 2F,NO.1135 XIANGYANG ROAD,MINHANGDISTRICT, SHANGHAI 201108,CHINA		CN		201108
8	NO.2588 SOUTH LIANHUA ROAD,MIN HANG DISTRICT,SHANGHAI,201100,P.R.C		CN		201100
9	NO.2588 SOUTH LIANHUA ROAD,MIN HANG DISTRICT,SHANGHAI,201100,P.R.C		CN		201100
10	NO.2588 SOUTH LIANHUA ROAD,MIN HANG DISTRICT,SHANGHAI,201100,P.R.C		CN		201100
11	NO.2588 SOUTH LIANHUA ROAD,MIN HANG DISTRICT,SHANGHAI,201100,P.R.C		CN		201100
12	NO.2 TAIGU RD WAIGAOQIAO FREE TRADA ZONE,SHANGHAI,P.R.C. AITG		CN
13	NO.2 TAIGU RD WAIGAOQIAO FREE TRADA ZONE,SHANGHAI,P.R.C. PECG		CN
14	NO.2 TAIGU RD WAIGAOQIAO FREE TRADA ZONE,SHANGHAI,P.R.C. WPIG		CN
15	NO.2 TAIGU RD WAIGAOQIAO FREE TRADA ZONE,SHANGHAI.		CN
16	NO.8 DATONG ROAD,NEW DISTRICT, SUZHOU.CHINA		CN
17	NO.8 DATONG ROAD,NEW DISTRICT, SUZHOU.CHINA PECG		CN
18	GROUND FLOOR SOUTH PART NO 161 RIJING ROAD CHINA (SHANGHAI)		CN
19	KUNSHAN XINING BONDED WAREHOUSE CO.,LTD 295 YANGGUANG RD,NANGANG TOWN,KUNSHAN		CN
20	NAN ZHA 5TH INDUSTRIAL ZONE HUMEN TOWN,DONG GUAN CITY DONG PROVENCE PEOPLES REPUBLIC OF CHINA		CN
21	NO. 2588 SOUTH LIANHUA ROAD, MIN HANG DISTRICT, SHANGHAI 201100 P.R. CHINA 310000		CN		201100
22	NO. 6 JINCHANG ROAD, HENAN INDUSTRIAL ZONE JINXIA, CHANGAN DONGGUAN, CHINA		CN
23	NO.1,NO.168 WEIHUA ROAD,PUDONG,SHANGHAI,CHINA		CN
24	NO.200 4/A FUTE ROAD(E),WAI GAO QIAO FREE TRADE ZONE		CN
25	NO.200 4/A FUTE ROAD(E),WAI GAO QIAO FREE TRADE ZONE		CN
26	NO.200 NORTH.FUTE RD,WAI GAO QIAO FREE TRADE ZONE,PUDONG,SHANGHAI,CHINA		CN
27	O. 2588 SOUTH LIANHUA ROAD, MIN HANG DISTRICT,		CN		200000
Sheet1

Test.xlsx
	A	B	C	D
1	Provinces in China	Provinces in China Abbreviation		City
2
3	Anhui	AH		Beijing
4	Beijing	BJ		Chongqing
5	Chongqing	CQ		Shanghai
6	Fujian	FJ		Tianjin
7	Gansu	GS		Hong Kong
8	Guangdong	GD		Macau
9	Guangxi Zhuang	GX		Anqing
10	Guizhou	GZ		Bengbu
11	Hainan	HI		Bozhou
12	Hebei	HE		Chaohu
13	Heilongjiang	HL		Chizhou
14	Henan	HA		Chuzhou
15	Hubei	HB		Fuyang
16	Hunan	HN		Hefei
17	Jiangsu	JS		Huaibei
18	Jiangxi	JX		Huainan
19	Jilin	JL		Huangshan
20	Liaoning	LN		Jieshou
21	Nei Mongol (Inner Mongolia)	NM		Lu'an
22	Ningxia Hui	NX		Ma'anshan
23	Qinghai	QH		Mingguang
24	Shandong	SD		Ningguo
25	Shanghai	SH		Suzhou
26	Shanxi	SX		Tianchang
27	Shaanxi	SN		Tongcheng
28	Sichuan	SC		Tongling
29	Tianjin	TJ		Wuhu
30	Xinjiang Uygur	XJ		Xuancheng
31	Xizang	XZ		Changle
32	Yunnan	YN		Fu'an
33	Zhejiang	ZJ		Fuding
Sheet2

 

[davesexcel]

I can only think of one way, loop through sheet 2 to find the items in sheet1

Little prep work.

Sheet2 column I use this formula.

Then use this code to filter sheet1 and place the data in the correct columns.

Sub get_Info()
    Dim ws As Worksheet, sh As Worksheet
    Dim rng As Range, c As Range, fRng As Range
    Set ws = Sheets("Sheet2")
    Set sh = Sheets("Sheet1")

    With ws
        Set rng = .Range("I2:I" & .Cells(.Rows.Count, "I").End(xlUp).Row)
    End With
    With sh
        Application.ScreenUpdating = False
        For Each c In rng.Cells
            If c > 0 Then
                .Range("$A$1").AutoFilter Field:=1, Criteria1:="=*" & c.Offset(, -8) & "*", Operator:=xlAnd
                Set fRng = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
                fRng.SpecialCells(xlCellTypeVisible).Offset(, 1) = c.Offset(, -5)
                fRng.SpecialCells(xlCellTypeVisible).Offset(, 5) = c.Offset(, -7)
            End If
        Next
        If .AutoFilterMode = True Then .AutoFilterMode = False
    End With



End Sub