VLookup Problem

[Sweden12]

I'm trying to use the VLookup formula to "extract" locations (cities) from a cell. I have tried different variations of the formula but couldn't get it to work. What I would need help with is that if one of the Locations in Column E is found in Column A the result should be in Column B. And if successful, the corresponding State (Column F) should be added to Column C. Since I have hundreds of locations to "extract", the regular "if" function is not ideal since it's easier to add the locations to a list then to manually add it to the "if" function. I would appreciate if someone could give me some input on how to solve my issue.

[63falcondude]

Try this in B2:

=INDEX(E$1:E$6,MATCH(TRUE,INDEX((ISNUMBER(SEARCH($E$1:$E$6,$A2))),),0))

Drag to the right and down.

[Sweden12]

That worked. You are awesome. Thank you so much for your help.

[63falcondude]

You're welcome. Happy to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.