How to assign country based off list of either provinces or states?

Question

I have a list of names and locations. Most of the locations include the name of the province or state. Some only include country names, some include state abbreviations.
I also have a list of provinces and states and which country they belong to, with common abbreviations.

How do I search the text of the Location column B for an inexact match, referencing the names of the states E:H, and return the Country that they belong to column C?

Answer 1

You can use the following formula

=ArrayFormula(IFERROR(VLOOKUP( 
                    IF(LEN(REGEXREPLACE(B2:B,"(.*,\s)(.*)","$2"))>2, 
                       VLOOKUP(REGEXREPLACE(B2:B,"(.*,\s)(.*)","$2"),F2:G,2,0), 
                       REGEXREPLACE(B2:B,"(.*,\s)(.*)","$2")),{G2:G,E2:F},2,0)))

Functions used:

• ArrayFormula
• IFERROR
• VLOOKUP
• IF
• LEN
• REGEXREPLACE