Formula processing is Extremly Slow

[MrSpark]

I have been using this formula which macthes the List of Cells to Raw String and if match found then write same value.

This function is working fine but it stucks and sheet gets stop due to the large set of data.

I would appreciate your help if someone can share a fastest way.

Sheet is stuck from past 10 minutes.

=INDEX($K:$K,AGGREGATE(15,7,ROW($K$3:$K450000$)/(ISNUMBER(SEARCH(" "&$K$3:$K$450000&" "," "&A3&" "))),1)

 

[MrSpark]

Yes you are right. I want to match US State column from Sheet "Match Criteria" to Sheet "Data" if matches then write.

I do not want to use MiD right and lieft function which similarly extract the streets number as well.

Because the data i have shared isuch cleaner than i m working on. Zipcode and state can be in any place in the string.

There are 2 column in Match Criteria sheet 1 with unique zipcodes and 2 with unique States

 

[Joe4]

If that is the "clean" data, I would hate to see what your actual data look like!
(It is extremely difficult to work with bad or inconsistent data).

That is quite the function you are working with, probably a bit beyond my level of expertise. Where did you come up with it?

If you are just trying to confirm that the Zip Code/State appears in the list on the other tab, I would probably work on just trying to extract those pieces, and then use a COUNTIFS formula to see if it appears on the other list (if it is greater than 0, then it does).

For complex issues, I usually look for VBA solutions too, but I cannot really fathom a good VBA solution here, especially because I think the biggest issue is the inconsistency of the data.
I am not saying one does not exist, but I cannot think of one myself.

Sorry I cannot be of more assistance.

 

[MrSpark]

Yes you are absolutlry right i have this data in very rough condition.

Yes i am trying to confirm that if Zipcode and State is exist in rough data by matching the already available zipcode and state in the "Match Criteria" sheet then write it.

I have seen this function online once i have used it but trying to find this i think its removed.

I just need to macth the existenace of zip and state and wirte in cell