Vlookup Partial match

Question

I have two excel sheet below:

Sheet1: My address data (company name + address)

Cong Ty Tnhh Vien Thong Huy Hoang (company name)
Đặng Cương An Dương Hải Phòng (address)
Công Ty Cổ Phần An Phương Nam (company name)
số 13/45 Phố chợ Đôn , Nghĩa Xá Lê Chân Hải Phòng (address)
Công Ty Cổ Phần Dầu Khí Quang Hưng
83/11 Đông Khê, Đông Khê Ngô Quyền Hải Phòng
số 83/11 Đông Khê, Đông Khê Ngô Quyền Hải Phòng
Công Ty Cổ Phần Liên Quân
Cát Bà Cát Hải Hải Phòng
Công Ty Cổ Phần Tm Và Dl Liên Quân
Cát Bà Cát Hải Hải Phòng
Công Ty Cổ Phần Tm Xnk Kiến Vàng
Vạn Mỹ Ngô Quyền Hải Phòng
Công Ty Cổ Phần Thương Mại Đầu Tư Và Phát Triển Minh Ngọc
831 Trường Chinh  tổ Trường chinh22, Quán Trữ Kiến An Hải Phòng
831 Trường Chinh , Quán Trữ Kiến An Hải Phòng
831 trường Chinh tổ 37, Quán Trữ Kiến An Hải Phòng
số 831 Trường Chinh , Quán Trữ Kiến An Hải Phòng
Số Nhà 831 Trường Chinh , Quán Trữ Kiến An Hải Phòng
Trường Chinh 831 , Quán Trữ Kiến An Hải Phòng

Sheet2: My province data

Ngô Quyền
Hồng Bàng
Hải An
Lê Chân
Kiến Thụy
Thủy Nguyên
Đồ Sơn
Kiến An
An Lão
Vĩnh Bảo
An Dương
Cát Hải
Tiên Lãng
Dương Kinh

My problem is:

I want to match address(sheet1) to province(sheet2) - to find province of this address - and display province in other column of sheet1

For example:

**Result** | **address**
#N/A       | Cong Ty Tnhh Vien Thong Huy Hoang (company name)
An Dương   | Đặng Cương An Dương Hải Phòng (address) //key is "An Dương"
#N/A       | Công Ty Cổ Phần An Phương Nam
Lê Chân    | số 13/45 Phố chợ Đôn , Nghĩa Xá Lê Chân Hải Phòng //key is "Lê Chân"

I didn't find any suitable solution on internet.

Answer 1

First put a "NONE" string to the first cell of your province data so that if the province is not found the formula will show that "NONE".

And write below formula to B1 of your address data (Sheet1) and press Ctrl+Shift+Enter in order to activate the array formula and copy down. When the formula becomes an array formula, you should be seeing { and } characters at the beginning and end of your formula.

=INDEX(Sheet2!$A$1:$A$15,SUMPRODUCT(ISNUMBER(FIND(Sheet2!$A$1:$A$15,A1))*ROW(Sheet2!$1:$15)))

• If your Province List has more than 15 items with the extra "NONE" row, then you should be changing your formula's Sheet2!$A$1:$A$15 & ROW(Sheet2!$1:$15) parts accordingly.

Here is the final screenshot when the formula is applied correctly: