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

  • Nik Fuentes
    Nik Fuentes ✭✭✭✭✭
    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

  • Nik Fuentes
    Nik Fuentes ✭✭✭✭✭
    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

  • Ronald Anderson
    Ronald Anderson ✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!