Behavior of "=" Operator

On several occasions I have confused myself, not understand why I am getting a #NO MATCH error when comparing a Text/Number column to a Auto-Number column, and I have realized why...

Results in False

  • 001 = 001
  • 099 = 099

Results in True

  • 100 = 100
  • 10000 = 10000

I understand why this is happening, but I do not necessarily agree with the way this behaves. Here's what I've concluded:

  • An Auto-Number column will always consider its contents as TEXT, regardless if it has no leading zeros
  • A Text/Number column will CONTEXTUALLY determine if the data in the cell is text or a number. Basically if there are leading zeros on a number, it will consider it text, but otherwise it will consider it a number
  • The equals operator is unable to contextually convert data types or at the very least return an error that explains a mismatch of data types. Example: it will simply tell you #NO MATCH if you use an INDEX/MATCH combo with a lookup value greater than or equal to 100 despite the real issue being a mismatch of data types. The error is misleading.

Potential Solutions/ Improvements

  • Add an empty string ("") to a Text/Number column value every time you want to compare it to a Auto-Number column
  • Update the "=" operator to contextually convert text containing a numerical value into a number before attempting to compare it to a number.
  • Throw a "mismatch of data type" error if attempting to compare text to a number
  • Give a visual indicator of whether or not data is being stored as text or a number, as it is not apparent unless you use the "ISTEXT" or "ISNUMBER" functions

Curious what others may think, as I frequently have issues with data not being the data type I expect it to be, and having no simply way of telling what the data type is by looking at it.

I've noticed Excel also behaves the same way with the "=", however it contextually converts text to a number when using arithmetic operators however Smartsheet does not... Not sure what the best way is at the end of the day.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!