根据“ID”查找值

Question

如何使用客户的地址和给定的“ProvinceID”在“客户表”中分配“CityID”？

(Province Table)
ID . ProvinceName
1 . . Ontario
2 . . Quebec
3 . . Nova Scotia
4 . . Quebec


(City Table)
ID . CityName . . . ProvinceID
1 . . Chatham . . . . . . 1
2 . . Chatham . . . . . . 2
3 . . Hudson . . . . . . . 1
4 . . Hudson . . . . . . . 2
5 . . New Glasgow . . 3
6 . . New Glasgow . . 4


(Customer Table)
ID . Address . . . . . . . . . . . . . . . . . . . . . . . ProvinceID . CityID
1 . . 123 aStreet1 St., Chatham, Ontario . . . 1
2 . . 123 aStreet2 St., Chatham, Quebec . . . 2
3 . . 123 aStreet3 St., Hudson, Ontario . . . . 1
4 . . 123 aStreet4 St., Hudson, Quebec . . . . 2

Wrongly inputed Address
5 . . Ontario Chatham 123 aStreet1 St. . . . . 1
6 . . 123 aStreet1 St. Chatham Ontario . . . . 1
7 . . Chatham 123 aStret St. . . . . . . . . . . . . 1

Partal (Incomplete) Address
8 . . 123 Street New Glasgow Scotia . . . . . . 5
9 . . 123 Street Glasgow Quebec . . . . . . . . . 4

Answer 1

=INDEX($A$11:$A$14,MATCH(1,(--(MID(B18,SEARCH(", ",B18)+2,SEARCH(", ",B18,SEARCH(", ",B18)+1)-SEARCH(", ",B18)-2)=$B$11:$B$14))*--(C18=$C$11:$C$14),0))

CTRL您需要使用+ SHIFT+将其作为数组计算应用ENTER。
使用 just 应用公式ENTER不起作用。