INSTR or IF Function - Picking out Specific text.strings in a Cell

[Sphinx404]

So I've run into a problem that I believe there is a solution for, however, I am not keen on how to implement the function.

In the chart below you'll notice that all of the FALSE returns highlighted are actually TRUE.

The code is as follows:

Range("AC2").Formula = "=IF(LEFT(K2,4)=LEFT(AB2,4),True,False)"

I'm pretty much just seeing if the first 4 letters of the USER DEFINED destination match the first 4 of the actual destination. What I need to be testing is: is "AA" contained in "AB".

I was told that I can use INSTR to get accurate results, but after looking at some examples, I don't see how declaring the "position" in which to start looking for a specific string like "Detroit" is going to help me.

It can't be this complicated; has to be simpler such as: IF "AB" contains "AA" then TRUE, if not FALSE

[TABLE="width: 629"]
<tbody>[TR]
[TD]Customer ID (Z)[/TD]
[TD]User Defined Destination (AA)[/TD]
[TD]Airport Code Destination (AB)[/TD]
[TD]Dest. Match? (AC)[/TD]
[/TR]
[TR]
[TD]DTW[/TD]
[TD]DETROIT[/TD]
[TD]Detroit, MI[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]SLC[/TD]
[TD]SALT LAKE CITY[/TD]
[TD]Salt Lake City, UT[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]MSP[/TD]
[TD]SAINT PAUL[/TD]
[TD]Minneapolis - St. Paul Int'l[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]MSP[/TD]
[TD]MINNEAPOLIS[/TD]
[TD]Minneapolis - St. Paul Int'l[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]HNL[/TD]
[TD]HONOLULU[/TD]
[TD]Honolulu, HI[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]SLC[/TD]
[TD]SALT LAKE CITY, UT[/TD]
[TD]Salt Lake City, UT[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]DTW[/TD]
[TD]DETROIT[/TD]
[TD]Detroit, MI[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]DTW[/TD]
[TD]DETROIT[/TD]
[TD]Detroit, MI[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]DTW[/TD]
[TD]DETROIT[/TD]
[TD]Detroit, MI[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]HNL[/TD]
[TD]HONOLULU[/TD]
[TD]Honolulu, HI[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]LAS[/TD]
[TD]LAS VEGAS[/TD]
[TD]Las Vegas, NV[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]EWR[/TD]
[TD]NEWARK[/TD]
[TD]New York - Newark, NJ[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]DTW[/TD]
[TD]DETROIT[/TD]
[TD]Detroit, MI[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]PHX[/TD]
[TD]SKY HARBOR[/TD]
[TD]Phoenix, AZ - Sky Harbor[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]ATL[/TD]
[TD]ATLANTA[/TD]
[TD]Atlanta, Hartsfield Atlanta[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]ATL[/TD]
[TD]HARTFIELD[/TD]
[TD]Atlanta, Hartsfield Atlanta[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]ATL[/TD]
[TD]ATLANTA[/TD]
[TD]Atlanta, Hartsfield Atlanta[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]ATL[/TD]
[TD]ATLANTA[/TD]
[TD]Atlanta, Hartsfield Atlanta[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]DTW[/TD]
[TD]DETROIT[/TD]
[TD]Detroit, MI[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]CLT[/TD]
[TD]CHARLOTTE[/TD]
[TD]Charlotte, NC[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]EWR[/TD]
[TD]NEWARK[/TD]
[TD]New York - Newark, NJ[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

 

[Sphinx404]

=ISNUMBER(SEARCH(AA2,AB2))

This can work, but its super restrictive. Is there a way to have it look for each word in the cell. For example... Saint Paul. Can I get it to look for Saint and Paul separately?

 

[steve the fish]

Its never going to find saint in st.

 

[Sphinx404]

Well, if I can't find things specifically then maybe I can at least reduce the number of FALSE returns I have... Using the first method from post #1 returns 751 FALSE... Using the ISNUMBER(SEARCH....) returns 530 FALSE. Of those 530, about 80% are actually true.

 

[steve the fish]

The ISNUMBER is your best bet. You could even use substitute to convert things like st. first such as:

=ISNUMBER(SEARCH(B2,SUBSTITUTE(C2,"St.","Saint")))

but i would rather either convert the data first before applying the formula.

 

[Sphinx404]

All AIRPORT DESTINATIONS from column AB (above) are found using VLOOKUP from a static list.

I don't know how, but I wonder if I can create some sort of a function that will search in that list.

Search For: Customer ID
Where: Airport Codes

When Customer ID is found, search all possible names (i.e A300:J:300)

Do any of the possible names match the user defined "AA" column?

There is a way to do this... I will find out how... well... I'll attempt to find out how.

 

[Sphinx404]

I made a table(new sheet) with all airport codes

column "A" contains the airport code (ATL)

column "B" contains the actual location of the airport

in columns C:K I've input all possible user definitions and left room (columns) to add additional definitions at a later date... so essentially "C:U".

So now I need to work through the formula and I have no idea how to encapsulate everything I want to do in a single formula without running 20 different VLOOKUPS.

1. I need to VLOOKUP the Airport Code in the new sheet (AirportCodes) =VLOOKUP(Z2,AIRPORTCODES!

2. Once the Airport Code is found in the list, I need to SEARCH for the User Defined Airport Code("AA") in the AirportCodes! list columns "$C$2:$U$2001"

3. If it finds a match THEN true/false

Anyone know how to write such a formula?