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)

 

[Joe4]

I see that your formula is checking all the way down to row 450000.
Do you REALLY have that much data?
If not, change that number to the lowest possible number that you feel will always cover all your data.
For example, if you know that you may have hundreds of row of data, but will never exceed 1000, change that number to 1000.

Note when you get large amounts of data, if you try to do database-type operations, performance can really take a hit.
That is often a clue that you are using the wrong tool for the job, and may need to investigate using a database solution, like Microsoft Access, or SQL.
Or maybe even investigate using Power Pivot, which allows you to do database-type operations within Excel.

 

[MrSpark]

I completely agree. Yes i have large ampount of Data even sometimes more than this.

But I did not used ever SQL so i was looking something in VBA. Because that is much faster than formula.

 

[Joe4]

Because that is much faster than formula.

Not necessarily, especially when it comes to doing any sort of matching.
I would strongly suggest you look into using Power Pivot, which is available in Excel.

 

[MrSpark]

Yes i heard about Power Query but never used, and You are still on point. I have bit understanding of VBA but not in Power Query. So i would request if something can be done in VBA then please do something. I would really appreciate it. Thanks

 

[Joe4]

Rather than try to decipher exactly what your formula is doing (I really do not have much experience with the AGGREAGTE function), could you show me a more complete example and explanation of exactly what your formula is supposed to be doing, i.e. show a sample of your raw data, and then your expected result based on that raw data?

 

[MrSpark]

Right Sir. I have attached a link of file where i have used a list of range like keyword to match in single string.

Zipstate.xlsx

1drv.ms

 

[Joe4]

Right Sir. I have attached a link of file where i have used a list of range like keyword to match in single string.

Zipstate.xlsx

1drv.ms

OK, I do not have the ability to download files from my current location (corporate security policy).
So if you are unable to post some screen images, then it will need to wait until I am after a computer in which I am able to download files from the internet.

 

[MrSpark]

Sure I have selected a cell D3 where formula is inserted. In this picture where you can see in active sheet i have data. and in Match Criteria Sheet i have Unique list of Zip Code and State.

 

[Joe4]

So what does the data on the Match Criteria sheet look like then?
Are you just trying to see if the Zip Code on the Data sheet is found on the Match Criteria page?