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

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

<tbody>
</tbody>

 

[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?