Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

is there a column number function for vlookup?

VLOOKUP requires a number for the column position in the table reference. But I have users who have been known to insert columns into the referenced sheet to the left of the needed column, throwing off the returned value. Is there any function I could nest inside the vlookup where I could provide the column *name* and either the sheet name or the sheet refence name and get back a number?

Answers

  • ✭✭✭

    Have you tried looking at Index and Match formulas? The great thing about that is that regardless where the column is moved, you'll be able to pull information from that column.

  • ✭✭✭

    Looks like I'd have to create a dedicated Range Reference for every column I pull in order to use INDEX(), which might be doable; probably less than 10 needed. Testing shows that Smartsheet does update the Range Reference when columns are inserted before the first range column, but not when columns are inserted in the middle of a multi-column range (which isn't surprising). So instead of one 15-column external reference, I'd have ten 1-column references.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions