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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 434 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!