Cross-sheet VLOOKUP maximums

Faith Glass-Wilson

With the new cross-sheet VLOOKUP function, how many VLOOKUP cells can exist in one sheet? Does the limit change if there are both inbound and outbound VLOOKUPS in the same sheet?

Daniel Stein

Hi Faith,

There are a couple of limits associated with cross-sheet formulas, within each sheet:

• You can reference up to 25,000 cells from other sheets.
• You can create up to 100 distinct named references.

If you re-use the same reference in multiple formulas (which is typical for VLOOKUP), it only counts once against these limits. So if you fill down a VLOOKUP formula in a column, it will not bring you any closer to these limits.

Hope this helps. For additional information about cross-sheet formulas, feel free to check out the help article.

Best regards,

Daniel

Product Manager, Smartsheet

MikeChap

Hello Daniel, 

I have a Smartsheet Business account with three users. I have run into the 100 distinct named references limitation. Seems like a very limitating boundary!

My spreadsheet for my business contains order numbers for different days of the week (5 days). The numbers are concerning 5 different entiities. Thus I have already (5 X 5) 25 different numbers. I would like to be able to present these numbers to my client for analyse (with the invoices) in two separate sheets, one with the numbers and one with the money amounts (number * article price). However I am only able to do four weeks referencing (maximum 100 cross references) from the numbers sheet to the money sheet. I would like to do the whole 52 weeks of the year!

Why is is this limit so low? 

The input source is from different persons. I hate the idea of having to split up the sheet due to restricting factors.

Anyway I copied the sheet into one for the numbers and one for the money amounts into separate sheets for week 1-4, week 5-8, etc.

Unfortunately and to my surprise, I still get the 100 cross reference limitation error even though the input source sheet voor week 5-8 has no output references and the output sheet for week 5-8 has no input references!

Evidently the limit is not only per sheet, but perhaps for the whole workspace?

Why does this limit immediately happen when I'm using new (copied) sheets without any existing cross references (all references deleted)?

I still have a original sheet (a third sheet) which does contain 100 cross references in my workspace.

Can you help me?

Thank you so much for your trouble.

Greetings,

Michael Chapman

 

 

J. Craig Williams

Mike,

It definitely sounds like there are omissions in your design. I think I'm stressing the functionality and haven't run into any show stoppers yet.

I just provided something like this:

http://ronin-global.com/2018/02/22/smartsheet-secure-your-sheets-with-x-sheet-references/

to a customer -- 20+ sheets (users) + main sheet with 20 or so columns being passed to or fro.

If you have a budget for help with the design, feel free to reach out to me or Richard Rymill at Smarter Business Processes. Info in my profile.

Craig

Daniel Stein

Hi Mike,

If you could paste some examples of the formulas you are using, we may be able to give you a few pointers here. Craig is right that there's typically a way to create a solution without using that many references.

Regarding copying sheets, if you want to remove the existing cross-sheet references then in the Save As New dialog you'll want to click "Data & Formatting Options" --> uncheck "Cell links & cross-sheet references". This will remove all cross-sheet refs from the new sheet and free you up to create 100 new cross-sheet refs.

It sounds like you may have tried to manually delete your cross-sheet formulas after copying the sheet. There is a delay of 2 hours before cross-sheet references are automatically cleaned up after deleting the formulas that use them, so you would still hit the limit until that happened. (The delay is intended to prevent formulas from breaking while customers are in the middle of editing sheets.)

Thanks,

Daniel

 

DwideSchrute

Hello,

I think the 25,000 cell limit is hard to work around.  We are pulling from a database with ~5000 rows that contain delivery information and that only allows me to lookup 5 columns.  Are there any plans to expand this limit?  Also being able to import sheets with more than 5000 rows would be great.

Daniel Stein

Hi DwideSchrute,

Thanks for the feedback. Raising the 25,000 limit is something we are looking into for a future release. It would be great to hear some more about your use case. How would you like to use lookups, and what would you need the limit to be?

Best regards,

Daniel

Cyndie LeClair

Good day Daniel!

I also am desiring to leverage this but ran into a dead stop with the limitations as I have a customer demographic file that I would like to populate key information so that our folks that complete the forms don't have to complete the back demographic data (about 7 columns that we pull in on their behalf).  However we have about 5K rows of data today so I am around 40K cell count and I can't split the spreadsheet into two as the vlookup aggregates so I am really stuck in a rock and a hard place.   Please look to increase this soon as this is a key initative for our organization. 

Daniel Stein

Thanks for the feedback. We will look into raising the maximums. In the meantime, I would recommend considering using INDEX and MATCH as an alternative to VLOOKUP. With INDEX and MATCH, you only need to reference the lookup column and the return column (not any intervening columns), so it will reduce the number of cells you are referencing, assuming your lookup & return columns aren't right next to each other. If you prefer to keep using VLOOKUP, you could try moving your return column as close to your lookup column as possible.

The typical usage of INDEX & MATCH is:

=INDEX(return_column, MATCH(search_value, lookup_column, 0))

• Summary: Looks up a value and returns a corresponding value from the same row but a different column (same as VLOOKUP)

• Arguments

  • return_column: The column of the value to return

  • search_value: The value to search for

  • lookup_column: The column in which to search

ryanswillie

I am trying to build a database of part prices and reference those in other sheets. With upwards of 15000 rows (multiple sheets), I am hitting a wall real quick. Please up the max rows per sheet to 10k and remove all limits to cell references. Thank you.

J. Craig Williams

If/when they do, you probably won't like the results. At least in the near term.

I've got 10 sheets, of varying size from 200 rows to 4000. Lots of interconnections. Slower than I would like. I'm at the limit on browser processing. Be able to add more rows is not going to help.

Craig

Ibrahim Khaleel

Dear All,

I am getting below error message.

"Some cross-sheet formula can’t be updated, because this sheet has more cell referenced from other sheets than the maximum allowed (100000)"

How can I fixed the above issue since I am maintaining history table and all completed records I am taking from historical table using Vlookup formula.

Need your suggestion and support.

Thanks and best regards.

Andrée Starå

Adding my answer here as well!

Hi @Ibrahim Khaleel

I hope you're well and safe!

To add to Paul's excellent advice/answer. (Converting to INDEX/MATCH instead)

• If needed, you could collect multiple columns in one and then split it out again in the destination sheet. I've done that a few times in my client solutions, even if I haven't hit the limit because it's less resource heavy.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!