Reverse wild card lookup | How to lookup when lookup value is in between a larger text?

Is it possible to do a lookup if the lookup value is not in a separate text and it is impossible to extract it? It is possible. And that is what you will learn in this video.
Often, when working with Vlookup, Match, or Xlookup functions, the lookup value is expected to be in a separate cell. But occasionally, the lookup value is in between a larger text and you do not have a way to extract it.
In that case, you can get it by using a combination of Index, match, ISerr (or iserror) and find function.
The illustration is shown in two parts: (i) The first one applies for Office 365 and (ii) Second part applies of Office 2019, 2016 or older versions as well as OpenOffice.

[@raghavanramasubramanian9279]

Brilliant solution

[@ProfectusLearning]

Thanks, Raghavan. I am glad you found it useful.

[@sriramakrishnanv6858]

It's a nice shortcut without using vlookup formula and no column splitting required too.

[@ProfectusLearning]

Actually, column splitting will not work here as some of the cities have two words (like New York)

[@YOUMOHAN1969]

You used a combo of match and Index. Very nice . Appreciate it . I took a longer route.

[@ProfectusLearning]

Thank you, Seshadri. I am glad you liked it.

[@user-lu2mp7cf8r]

Genius

[@Talib_Husain18]

How were you able to clearly see the errors within the formula cell ?