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
-
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.
-
"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?
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!