IF Vlookup formula locking up file

[mugengold]

Hello, I am currently using the following formula and it is causing massive lag on my sheet.

=IF(ISNUMBER(XMATCH($A3&C$1,'LIST'!$A:$A&'LIST'!$B:$B)),"X","")

It becomes basically unusable to the point where i turned auto calculations off. Is there a better cleaner way to reach my desired result that wont slow down the file?

Here is an example of the data that I have. This has been shortened, the true table has roughly 1000 each of TYPE and COLOR that will continue to grow. I think the large volume of IF statements is cause of the slowdown.

This would be the desired result

Any helpwith this would be greatly appreciated!!

 

[Fluff]

Hi & welcome to MrExcel.
The 1st thing to do is stop using entire column references & limit the range to something more sensible.

=IF(ISNUMBER(XMATCH($A3&C$1,'LIST'!$A$2:$A$10000&'LIST'!$B$2:$B$10000)),"X","")

 

[Joe4]

Welcome to the Board!

One thing that might help is to NOT use full column references like $A:$A and $B:$B! That makes it look at every single cell in the column (which is over one million)!
Much better to pick a hard end, like $A2:$A10000.
If the ending row is always changing, just pick the smallest number that is large enough that you will never exceed it.

EDIT: Looks like Fluff and I had the same idea!

 

[mugengold]

OK, I've implemented the change and it is working significantly faster now but some lag does remain. The "calculating 16 threads" notice pops up at the bottom.

 

[Fluff]

How many rows did you limit the formula to?
With 10,000 rows it's instant for me (with only 6 threads)

 

[Joe4]

OK, I've implemented the change and it is working significantly faster now but some lag does remain. The "calculating 16 threads" notice pops up at the bottom.

Also, I am guessing maybe you also have other calculations happening on your sheet?
Those could be coming in to play also.

 

[mugengold]

How many rows did you limit the formula to?
With 10,000 rows it's instant for me (with only 6 threads)

I used the 10000 as you suggested.

Currently the formula is in around 800,000 cells for my table

The "calculating" pops up when I save and when I filter

 

[mugengold]

Also, I am guessing maybe you also have other calculations happening on your sheet?
Those could be coming in to play also.

I do have multiple tabs in the workbook that may be doing other things. I inherited this particular beast. but as for my fresh tab I created, this is the only calculation

 

[Fluff]

Currently the formula is in around 800,000 cells for my table

That would be the reason.

Not sure if this will be any faster, but you could try

=IF(COUNTIFS(LIST!$A$2:$A$10000,$A3,LIST!$B$2:$B$10000,C$1),"X","")

 

[mugengold]

That would be the reason.

Not sure if this will be any faster, but you could try

=IF(COUNTIFS(LIST!$A$2:$A$10000,$A3,LIST!$B$2:$B$10000,C$1),"X","")

if its just purely based on volume then I have to live with it. My main concern was that my code was just inefficient, which we did solve to a degree. I will try new code to see if that improves lag time.