Performance issues with LOOKUP() formula

George Thomas Irimben

Hi,

I have a Smartsheet with around 3600 rows and ~20 columns. After introducing a simple LOOKUP() into one column of these 3600 entries, the Smartsheet page has become dead slow. Browser is giving error "An application is slowing down ...". Tried opening this sheet in both Firefox and Chrome browsers but same result.

Formula is like this:

=LOOKUP([Serial No]1, [RefTableColA]:[RefTableColB], 2, false)

How to resolve this issue? Any suggestions?

 

Thanks

-George

Shaine Greenwood

Hi George—

It appears you're treating the span of multiple columns (which contain 3600 cells each) as your lookup table. This is not a simple LOOKUP function when it has to iterate over 7200 cells to find the value each time the sheet is opened. 

Smartsheet is ultimately a browser based application, and as such is limited to browser speeds. Even when using the latest version of Chrome (which works best with Smartsheet), you'll likely have slowdown with this type of function.

To solve this, reduce the lookup table you're referencing such that it doesn't span the entirety of multiple columns.

George Thomas Irimben

Thanks!

I understand huge number of cells for lookup is causing the issue. But, I feel this slowdown should happen only at the loading time or if any edits completed in relevant cells.

Thoughts?

-George

Shaine Greenwood

Since you're referencing the range of multiple columns, this slowdown can occur anytime you add a single row, as when you do that, you're expanding the range of the column—which causes the formula to update its understanding of how many cells to look at. Slowdown can also occur, as you put, on initial load and when cells in those columns are edited.

There are many changes that might trigger the formula engine to think, "I need to recalculate something or store new information."

George Thomas Irimben

I see the slowdown issue even with arrow key presses.. This makes the sheet unusable even for viewing the content.

Unless there is a *value change* in the sheet, I don't think lookup engine need to recalculate.  Pls comment.

Shaine Greenwood

George—

As you stated, your sheet got slow when you put the LOOKUP function in it, that tells me that the LOOKUP function, or the argument selection (your lookup table) is the cause of the issue.

You need to remove the LOOKUP function from your sheet altogether in order to troubleshoot the slowness.

If you remove the function and your sheet stops being slow, it's referencing too much data and the range needs to be reduced.

If you remove the function, save your sheet, and it's still slow, then contact our Support team for further troubleshooting.

George Thomas Irimben

I understand size of lookup table(~tried with 2000 rows) is causing this slowdown issue.

My question is why the sheet has to be slow(after loading), when I am just moving the cursor around using arrow keys.

May be, it is a limitation in Smartsheet.. Will contact Support team..

Thanks for help.

 

Shaine Greenwood

Please do—there could be an unknown factor that I'm not able to see or troubleshoot for that the Support team can check out.