100,000 cell reference limit

Random

I'm building a smart sheet report which pulls data out of an excel report (which I imported into smart sheet) of total 150 columns. I'm using Vlookup formula to identify the data column I need with a key column value.

Strange thing is, the Vlookup worked when I reference the sheet field of column 1-130, but then randomly it shows me error msg of 100000 cell reference limit when I select the reference field of column 1-99. I tried many times with exactly the same step (clicking the whole columns instead of specific rows of data), but some times it worked with more columns included but some didn't work with less columns.

I couldn't figure what I did wrong?

I can not use index formula to solve this because the base data report will be refreshed and downloaded every week with added/reduced rows and key value in rows shifting row position.

Can someone tell me what I did wrong?

Random

oh i found the solution myself. because all my references are from the same sheet, instead of creating different range of references for each column, i include all the columns in range one and always refer to the same range for references in formula, that way no double counting and it worked!

Random

oh i found the solution myself. because all my references are from the same sheet, instead of creating different range of references for each column, i include all the columns in range one and always refer to the same range for references in formula, that way no double counting and it worked!

mbsamuel6

Can you or anyone else explain this more. I am facing the same issue and not sure what you mean for your solution. How did you include all columns in range one?