VLOOKUP in TABLE with User Defined Argument

[Sphinx404]

I have a problem that I'm not sure can be solved in a simplified manner.

What the code below is doing:

Takes the LEFT 3 characters on sheet1 (airport code) and looks up the airport code in sheet2 and returns the 2nd column (city). Before returning TRUE or FALSE, it tries to see if the first 4 characters of the USER DEFINED city matches the first 4 characters of the city in the table on sheet2. If it does, then TRUE, if not then FALSE.

Range("AA2").Value = "=LEFT(TRIM(Q2),3)"
Range("AB2").Value = "=IFNA(VLOOKUP(AA2,AirportCodes!$A$2:$C$2000,2,0),""No Departure"")"
Range("AC2").Value = "=IF(LEFT(K2,4)=LEFT(AB2,4),True,False)"


Range("AA2:AC2").Select
Selection.AutoFill Destination:=Range("AA2:AC50000")

The problem is, I have so many FALSE returns that should be true. While the first 4 characters might not match, somewhere in the string, it would match.

For Example"

USER Defined City: NARITA
Sheet2 Defined City: TOKYO - NARITA

or

USER Defined City: LOGAN/BOSTON, MA
Sheet2 Defined City: BOSTON, - LOGAN, MA

or

USER Defined City: NEWARK
Sheet2 Defined City: NEW YORK - NEWARK, NJ

I know I can use a CASE statement, but I can't do that for each any every possible user definition:

Case "NARITA"
    If myrange.value LIKE "*NARITA*" then
        mycell.Offset(,7) = "TRUE"

Besides doing a CASE statement, do any of your VBA Pros have a better solution?

THANK YOU!

 

[Joe4]

If wanting to use Excel functions, try using the FIND or SEARCH functions to see if the one 4 character string is located inside the other. If it is found, it will return a number greater than 0.
Or just use the INSTR VBA function. See: https://www.techonthenet.com/excel/formulas/instr.php