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

Find more posts tagged with

Formulas

Cell linking

Accepted answers

Nik Fuentes

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

All comments

Nik Fuentes

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.

Ronald Anderson

Thank you Nik! Unfortunately, Smartsheet choked on my data.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.