VLookup Not Working as Expected


I have a short list of company codes and head count in a simple table. When i attempt to do a simple vLookup from another table, i get inconsistent results although the values are in the lookup table. I strongly suspect it is user error but i have looked at it for so long i can't seem to rationalize it. Any help would be greatly appreciated. Thanks in advance.

This is the lookup table. Both the Code and TechUsers columns are configured at Text/Number.

This is the sheet i am using to test the vLookup. I did a copy/paste of the Code column to this sheet from the lookup table. Couple of things i observed. The first is that even though the matching row is in the lookup table, i still get a #no match for some of the rows. Next, each row in the lookup table has a unique value for the number of users. However, if you look at JLG, the vlookup provided a value for a different row in the lookup table. There are multiple instances of this.

This is a screenshot of the reference setup between the two sheets. I created the reference and selected the header for the three columns.

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Hi @barrilleauxap ,

    Add "false" to the end of your formula so it searches for an exact match.

    =VLOOKUP([primary column]@row, {business headcount}, 3, false)


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!