Index/Match (any limitations?)

I'm using index/match to reference account parameters on a master sheet from other sheets (sheet A, sheet B, sheet C, etc.). How well will this scale up? Can you use index/match to pull values from the master sheet to an infinite number of sheets or is there a limit to the number of times you can query the master sheet (or a limit to the number of requests made by the supported sheets A,B,C,...). I hope this makes sense. Thanks!

Find more posts tagged with

Comments

Isaac Jose

Hello,

Thanks for your post! It sounds like your wondering what the limitations are for cross sheet references in your formula specifically regarding the INDEX/MATCH functions. There are a few limitations that you should be aware of:

A single sheet can contain up to 100 unique cross-sheet references in formulas. Each cross sheet reference can be used in any number of formulas, but only 100 unique references may be created.
The cross-sheet references in a sheet may not exceed 25,000 unique referenced cells from other sheets. If one of your cross sheet references looks at two columns on a sheet with 100 rows, this will count as 200 cells toward the 25,000 cell reference limit.

The above information can also be found in our Help Center here (https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets).

If you think you may exceed these limitations, I would recommend creating a copy of the master sheet and deleting the cross sheet references in the copy so that you can create new ones. You can then consolidate both of these sheets into a single Report (Building Reports https://help.smartsheet.com/articles/522214-creating-reports). Note that cross-sheet references take about 30 minutes to an hour to be dissolved from a sheet after deleting them from the cells, so if you reach the limit, copy the sheet, and delete the current references, then you'll need to save the sheet and wait some time before you're able to add different references.

I hope this helps!

Isaac J

Smartsheet Support

Mike L.

Isaac, thanks for the great info. If sheet A contains 5 unique cross sheet references (each filled down through 1000 rows) in order to look up values on the master sheet and I delete all of the rows in sheet A, does that also delete (free up) those 5000 previously used cross sheet references? Thanks!

Isaac Jose

Hi there,

I know I'm extremely late on a response here (I guess I don't have community notifications set up!) but I wanted to circle back since I stumbled across this post while working on something else.

To answer your question, deleting those 1000 rows will indeed free up the 5000 previously used cross sheet references.

Regards,

Isaac J

Smartsheet Support

Sign in to join the conversation!

Quick Links

Trending discussions

Does anyone else find Collections worthless?
I find Collections worthless, and am wondering if others feel the same. If these are replacing WorkApps I have so many questions. The biggest gap by far (IMO) are managed references. If the whole point of Collections is to control access and only share certain things, then this is not going to work for me. For example, I…
Can input from one form separate to two rows when they input to sheets based on conditions?
We currently ask people to select one form per one need for referral into a program. For example, I want to submit a referral to a community health team and another one to behavioral health team. I need to submit two separate forms because each form submission will create one row to master task list where it then auto move…
Archive Project w/o Control Center
We do not own Control Center. What is the best practice for archiving projects?