Vlookup Limitation?

Vivien Chong

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.

 

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:

1. {Column with the value to return from the other sheet}
2. {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:

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

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