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

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.