Adding new columns into a sheet that has vlookup formulas referencing it

Katrina Walker

If I am referencing a sheet in a vlookup command from another sheet and I add a column to the sheet that I am referencing and it is not at the end of the sheet but before the column I am using in my vlookup formula then it will push the column number out by 1. The vlookup then references the wrong column?

Is there any way around this apart from adding the column right at the end or changing my vlookup?

Thanks

Katrina

Jeff Reisman

@Katrina Walker

Using INDEX/MATCH in place of Vlookups eliminates the column position issue, because it's referencing specific column ranges by a behind-the-scenes column ID that doesn't change no matter where the column is moved to in the sheet (or even if the column is renamed.)

The basic syntax is:

INDEX({Reference to the range you want a value from}, MATCH(Value@row, {Reference to range in other sheet containing the same value}, 0))

Use Smartsheet's helper box when typing your formula, in order to create your cross-sheet references as you type.

Katrina Walker

Thank you for the quick answer Jeff. I will go try this out right now.

Katrina Walker

@Jeff Reisman just wanted to let you know that I changed all my vlookup to Index/Match and what a difference! Thank you again for your assistance. Love it that you can Index and then more than one match too.

Katrina

Jeff Reisman

https://community.smartsheet.com/discussion/comment/323582#Comment_323582

Glad it worked for you!