0

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?

Screenshot of example data

1
0

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)))

Vlookup in multiple columns

Functions used:

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.