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
- Smartsheet Customer Resources
- 63.6K Get Help
- 404 Global Discussions
- 215 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!