100,000 cell reference limit
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?