Vlookup Limitation?

Hi I would like to understand about this limitation in vlookup:

Each sheet can include no more than 100 distinct cross-sheet references.
There’s a limit of 25,000 inbound cells that can be referenced from other sheets into one sheet in total.

(Reference: https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets?_ga=2.14620343.1874386604.1574040931-1620381183.1573536453)

What do you mean by 100 distinct cross-sheet references?

- If referencing to the same table, same criteria, is it consider 1 distinct cross-sheet reference?

Limit of 25,000 inbound cells?

- The destination sheet can only "receive" 25,000 inbound data?

- If we have 1 master sheet that will serve as database and need to be outbound to many sheets, can the outbound be more than 25,000 cells? Our master sheet contains 15 columns with 4000 rows.

We have start building a database where information can be pull out to other sheet to standardise the data input, we are at the end of the solution building when we realise this limitation.

Appreciate advice and help in understanding this better.

Find more posts tagged with

Human Resources

Manufacturing

Formulas

Comments

Paul Newcome

Distinct Cross Sheet References are each of the individually named ranges.

{Sheet A Range 1} can be used 100 times but only count as 1 distinct range.

{Sheet A Range 2} would now give you 2 distinct ranges, so on and so forth.

If your range encompasses multiple columns and/or rows, it will still only count as 1 distinct range.

You can link OUT to the other sheets from your master sheet as much as you would like. The limit is what is coming IN, and that limit is 25,000 cells.

If you have a range that is a single column and 100 rows long, that is 1 Distinct Cross Sheet Reference and counts as 100 inbound cells. It can be used as many times as you would like though, so even if you used it 500 times, it would still only count as 1 distinct range and 100 inbound cells.

Vivien Chong

Thanks Paul. Your explanation is simple and clear.

Appreciate it very much!

Paul Newcome

Happy to help!

smallplanet111

Not understanding, what is Range 1 and Range 2 difference?

Genevieve P.

Hi @smallplanet111

These are two different columns or ranges being referenced from another sheet. Here's an example formula that might use two ranges:

=INDEX({Column with the value to return from the other sheet}, MATCH([Value to match]@row, {Column with the value to match in the other sheet}))

In this instance, there are two different ranges:

{Column with the value to return from the other sheet}
{Column with the value to match in the other sheet}

Here is a picture of what the first range, {Value to Return}, might look like:

Screen Shot 2020-02-17 at 9.42.01 AM.png

Then this would be a second, different and distinct range with a different name, {Value to Match}:

Screen Shot 2020-02-17 at 9.42.14 AM.png

Since these two ranges reference different columns, they are 2 distinct references with their own specific name. Does that help clarify? Let me know if I can explain anything further.

Cheers,

Genevieve

Sign in to join the conversation!

Quick Links

Trending discussions

How do I get a column that shows the automated % complete for each activity?
I am struggling to get any formulas to work, I need a column that shows the percentage complete from the start date to the finish date - shouldn't be that hard but this is causing me so much grief. Every formula I find doesn't seem to work and I am not savvy enough to know how to update the formula so that it can work.…
Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?