Add an apostrophe before a number entered into a form

Smartsheet WIP

I used an INDEX/MATCH formula in my sheet to populate the clients name on the sheet when the client number is entered on a form. When the number gets to the sheet, it returns "#NO MATCH" in the cell until I add the apostrophe before the number. Is there a way I can add the apostrophe from the form so it will validate when it hits the sheet?

Danielle Arteaga

Want to make sure I'm following your issue - are you saying that when a user enters a client number using your form, Smartsheet is adding an apostrophe (') to the number when the form data is added to your worksheet? Are you using a Smartsheet form, or are you importing the data from Excel? The solution will vary depending on which method you're using.

Carson Penticuff

"When the number gets to the sheet, it returns "#NO MATCH" in the cell until I add the apostrophe before the number."

Does the client list you are matching against have an apostrophe before each name?

amhba

This looks like a datatype mismatch error.

I believe that INDEX/MATCH only works (or at least in my experience, seems to work best) on text datatypes, so this sounds like a datatype mismatch error, which is generally happens when running INDEX/MATCH using input values that are only numerals.

It is easy to convert your data to text without having to modify the "real" data, though, and I would recommend doing that if you can.

Consider using a helper column with a column formula to convert the value to a text datatype. Do the same in the reference sheet that you are comparing this to, then MATCH on those text-converted values. This way, when you compare MATCH values, you are certain that you are comparing values that are the same datatype. It is easy to convert Smartsheet data to a TEXT datatype: Simply add +""

Example in sheet:

Input Column | Converted Input Column

Input | =[Input Column]@row + "" (this would be a column formula)

Sample Index/Match:

=INDEX({Desired corresponding return value array from reference sheet}, MATCH([Converted Input Column]@row, {text-converted input column values in reference sheet array}, 0))