Adding a Column in a Cross Sheet Reference Range on VLOOKUP

Jim Hook

I'm exploring changing several of my more complex Smartsheets to rely less on links and more on data tables that are accessed by other sheets using VLOOKUP functions. Everything was fine until I needed to add a new column on one of the data tables and discovered that the named ranges that use that table don't automatically update to account for an additional column in the middle of a range as I expected, or at least hoped. In other words, it looks like I need to go edit every VLOOKUP function that references columns to the right of where I inserted the new column and add one to the column number to retrieve the desired data.

Maybe one of the Smartsheet support people can verify that this is how it's supposed to work. I suspect that the ranges are associated with the sheet that uses the VLOOKUP function so it has no idea that the external reference range has changed. Links appear to be based on cell-to-cell connections so inserting new columns, moving columns around or moving rows up and down doesn't impact them.

Andrée Starå

Hi Jim,

If you have a VLOOKUP range of 10 columns and you add another one in that range, it will still only reference 10 columns. One will not be included. 

What I do if I’m working with a VLOOKUP structure is that I add the column(s) after the initial range and then update the range instead, and in that way, everything will still work. The only issue might be that you’d want to show the column somewhere else in the sheet. 

If my client solution permits it, I try to use INDEX/MATCH instead because it’s much more flexible and not so constrained.

Make sense?

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!