CODEWITHSUNDEEP
excelexcel-formula
10101
10101

Reputation: 2412

Lookup for specific text in cell and VLOOKUP if it contains

I have range with IATA codes in $B$2:$B$3591 and destination names with IATA codes in $A$2:$A$3591. I would like to search for IATA code based on values in range $A$2:$A$3591. The problem is that I can't use exact match as exact text can't be entered.

For example if I type Bromma, formula should display BMA. If I type Arlanda, formula should display ARN and etc.

         A                                  B
Stavropol, Russia (STW)                    STW
Stavanger, Norway (SVG)                    SVG
Stella Maris, Bahamas (SML)                SML
Stockholm, Sweden - All airports (STO)     STO
Stockholm, Sweden - Arlanda (ARN)          ARN
Stockholm, Sweden - Bromma (BMA)           BMA
Stornoway, United Kingdom (SYY)            SYY
Storuman, Sweden (SQO)                     SQO
Strasbourg, France - Bus service (XER)     XER
Strasbourg, France - Entzheim (SXB)        SXB
Stronsay, United Kingdom (SOY)             SOY
Stung Treng, Cambodia (TNX)                TNX

I have tried =VLOOKUP(E3&"*";$A$2:$B$3591;2;FALSE) so that in E3 I type Bromma but it does not work. However this works for Stavropol for example.

Upvotes: 1

Views: 1397

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

E3$"*"

Will only match the begining of the the text. Add another wild card for the start:

"*"&E3&"*"

To Match on text anywhere in the string.

Upvotes: 2

Related Questions