Behavior of "=" Operator

preston.murphy@vallourec.com

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.