Add an apostrophe before a number entered into a form

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?

Answers

  • Danielle Arteaga
    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
    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
    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))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!