COMBINATION - ISNUMBER, SEARCH, VLOOKUP - Ideas?

[Sphinx404]

Hi Experts,

Need some help with putting this function together for my macro...

=ISNUMBER(SEARCH(VLOOKUP

#1 I need to VLOOKUP the Customer ID ("Z") in "AirportCodes" sheet
#2 Once found (i.e. EWR) I need to use the User Defined Destination ("AA" - Sheet1) and see if it exists in any of the columns "C:U" (AirportCodes! sheet)
#3 If the user defined destination is found, then true, otherwise false.

As you can see, the chart below shows values that are FALSE but should've returned true... but it's comparing the user defined destination with the actual destination, so I am trying to come up with a solution to account for all the possible user definitions of a single location.

The AirportCodes sheet is a sheet that has 2 columns "A" is the Airport Code (ATL), column "B" is the actual location

In columns "C:U" I've added in as many different ways of spelling something as I can think of... and over time I'll continue to add more
For example:
MSP - CODE
Minneapolis/Saint Paul - Actual Location
St. Paul, STPAUL, Saint Paul, ST. Paul, St.Paul, MN, saintpaul, mn,... etc etc. etc. - User Definitions

CAN ANYONE HELP or offer their advice? Thank you!

 

[AFPathfinder]

Shot in the dark, give this formula a go in cell AC2

=IF(ISNA(MATCH(AA2,ADDRESS(MATCH(Z2,AirportCodes!$A:$A,0),3)&":"&ADDRESS(MATCH(Z2,AirportCodes!$A:$A,0),21),0)),FALSE,TRUE)

It's a crazy blender of functions, but I think it works.

 

[Sphinx404]

Shot in the dark, give this formula a go in cell AC2

=IF(ISNA(MATCH(AA2,ADDRESS(MATCH(Z2,AirportCodes!$A:$A,0),3)&":"&ADDRESS(MATCH(Z2,AirportCodes!$A:$A,0),21),0)),FALSE,TRUE)

It's a crazy blender of functions, but I think it works.

Thank you so much for helping me out with this.

I initially got it to work without actually testing the results just yet. However, I'm trying to input it into VBA, but it's a DEBUG nightmare.... as these always are for me. I can never figure out where the "," and the ")" go. Any help you can continue to provide would be appreciated. Any idea where the extra parenthesis' or commas go?

Range("AB2").Formula = "=IF(ISNA(MATCH(AA2,ADDRESS(MATCH(Z2,AirportCodes!$A:$A,0),3)&":"&ADDRESS(MATCH(Z2,AirportCodes!$A:$A,0),21),0)),FALSE,TRUE)"

 

[AFPathfinder]

I know exactly what you mean. The relative references always confuse me if I'm typing by hand. I normally put the formula into a cell, then type "? Selection.FormulaR1C1" in the immediate window to get the conversion to R1C1. The quotes are difficult, but you have to substitute each one with: & """" &. Here is what I got for that formula:

    Range("AC2:AC" & finalRow).FormulaR1C1 = "=IF(ISNA(MATCH(RC[-2],ADDRESS(MATCH(RC[-3],AirportCodes!C1,0),3)&" _
        & """" & ":" & """" & "&ADDRESS(MATCH(RC[-3],AirportCodes!C1,0),21),0)),FALSE,TRUE)"

For some reason, the R1C1 replaces "$A:$A" with "C1" and it seems to work just fine. I can't explain that one, but ok.

 

[Sphinx404]

I got it to work! Thank you!

I replaced finalRow with myLastRow per my variable...

It also kept giving me object errors, so I took out the .FormulaR1C1 and input just .Formula and it all of the sudden worked.

I do appreciate your help @AFPathfinder , not even one of the MVPs tried this one, so kudos to you. This more or less completes the macro.

Much obliged,

Steve

 

[AFPathfinder]

You're welcome! It was a fun one once I started digging into it.