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

  • kioshi43
    kioshi43 ✭✭✭

    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.

  • Ke6n
    Ke6n ✭✭

    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!