INDEX COLLECT or VLOOKUP not working with numbers
I am in need of some help with trying to use either the INDEX/COLLECT or VLOOKUP functions.
I have tried everything I can think of and nothing works. By employee number, I want to return their tenure. Both are contained in a reference sheet. Here is what I have tried.
=IFERROR(INDEX(COLLECT({Tenure }, {Employee Number}, [Employee Number]@row), 1), "Not Found")
=IFERROR(VLOOKUP([Employee Number]@row, {Tenure Lookup}, 2, False), “Not Found”)
Source Sheet:
Reference Sheet:
Thank you - Ron
Best Answer
-
so I did a test with some dummy data from a scrap sheet I had and the INDEX(COLLECT()) function seems to be working, so your syntax is correct.
If you remove the IFERROR() part, what error message does it throw? If it's a #NO MATCH error, there might be something up with the way the two [Employee Number] columns are formatted. Maybe 1 has a space in it or something?
Every once in a while, Smartsheets will choke on a function or a cell for no apparent reason, no matter how correct it is and no matter what you do to fix it. there's no reliable way I've found to see if that's what's happening, so you'd have to test it yourself. I recommend setting up a completely new scrap sheet and recreating the formula there with 1, maybe 2 Employee Numbers to test. If it works there, Smartsheets is choking and you'll need to delete whatever column/cells the broken version was working in on the source sheet and recreate it again from scratch [don't copy/paste]. If it doesn't, I'm not entirely sure what's going on
Answers
-
so I did a test with some dummy data from a scrap sheet I had and the INDEX(COLLECT()) function seems to be working, so your syntax is correct.
If you remove the IFERROR() part, what error message does it throw? If it's a #NO MATCH error, there might be something up with the way the two [Employee Number] columns are formatted. Maybe 1 has a space in it or something?
Every once in a while, Smartsheets will choke on a function or a cell for no apparent reason, no matter how correct it is and no matter what you do to fix it. there's no reliable way I've found to see if that's what's happening, so you'd have to test it yourself. I recommend setting up a completely new scrap sheet and recreating the formula there with 1, maybe 2 Employee Numbers to test. If it works there, Smartsheets is choking and you'll need to delete whatever column/cells the broken version was working in on the source sheet and recreate it again from scratch [don't copy/paste]. If it doesn't, I'm not entirely sure what's going on
-
Thank you Nik! Unfortunately, Smartsheet choked on my data.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 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
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!