Extract province (list included in different sheet) from Address (Advanced fussy search)

[a331005]

Pls find the attached file.


https://drive.google.com/file/d/1gEUX8QJ8rugKYACuYRISCAI3MmWlOM5i/view?usp=sharing

What im looking to get is province name in coloumn B of "Address" sheet. Province names are listed in the sheet "Province"

Any help is greatly appreciated. Thanks for your time.

 

[Eric W]

Welcome to the forum!

Try this in B1:

=LOOKUP(2^16,SEARCH(province!$A$2:$A$204,A1),province!$A$2:$A$204)

There's a possibility of error if a province name can be found in a street name, or if there are misspellings, but it should mostly work for you.

 

[a331005]

Thanks a ton Eric. You really saved me a lot of work

Now Im dealing with mis-spelled words using the resultant coloumn as a helper coloumn and using VLOOKUP. Im sure there is a smarter way to this combining my vlookup to the existing formula =LOOKUP(2^16,SEARCH(province!$A$2:$A$204,A1),province!$A$2:$A$204)


Also how to reference a growing list (table) instead of province!$A$2:$A$204, where when list grows.

=LOOKUP(2^16,SEARCH(placeDB,[@Address]),province!$A$2:$A$12) Works, but
=LOOKUP(2^16,SEARCH(placeDB,[@Address]),placeDB) doesnot work. is there a way to name the [result_vector], so i dont want to edit everytime, as my list grows.

 

[a331005]

Updated work
https://drive.google.com/file/d/1gEUX8QJ8rugKYACuYRISCAI3MmWlOM5i/view?usp=sharing

 

[a331005]

Updated work
https://drive.google.com/file/d/1gEUX8QJ8rugKYACuYRISCAI3MmWlOM5i/view?usp=sharing

Thanks a ton Eric. You really saved me a lot of work

Now Im dealing with mis-spelled words using the resultant coloumn as a helper coloumn and using VLOOKUP. Im sure there is a smarter way to this combining my vlookup to the existing formula =LOOKUP(2^16,SEARCH(province!$A$2:$A$204,A1),province!$A$2:$A$204)


Also how to reference a growing list (table) instead of province!$A$2:$A$204, where when list grows.

=LOOKUP(2^16,SEARCH(placeDB,[@Address]),province!$A$2:$A$12) Works, but
=LOOKUP(2^16,SEARCH(placeDB,[@Address]),placeDB) doesnot work. is there a way to name the [result_vector], so i dont want to edit everytime, as my list grows.

 

[Eric W]

It looks like you made a table out of your addresses, but did you make one out of your province list? I used this formula, with the province list in a table named Table3:

=LOOKUP(2^16,SEARCH(Table3[province],[@Address]),Table3[province])

 

[a331005]

thanks again eric

It looks like you made a table out of your addresses, but did you make one out of your province list? I used this formula, with the province list in a table named Table3:

=LOOKUP(2^16,SEARCH(Table3[province],[@Address]),Table3[province])