VLOOKUP data type question

Mike L.
Mike L. ✭✭✭
edited 12/09/19 in Formulas and Functions

I'm using this vlookup formula to pull back a value from a data sheet.  

Let's say the value of NUMBER81 is 123456 and when I type in the formula on the desire sheet it returns "No match".  

When I go to the ACCOUNTS sheet and look at the data there is a value equal to 123456 in Range 1. 

When I put an apostrophe in front of the value so that it is entered as '123456 in the cell then the vlookup statement works. 

=VLOOKUP(NUMBER81, {ACCOUNTS Range 1}, 2, false)

Since the accounts sheet is manually maintained by me I can put an apostrophe in front of every search value in the NUMBER column. 

Back in the sheet where I put the VLOOKUP formula. This data is imported from excel so on closer inspection of the column data used for the search value I found that each "number" has an apostrophe in front of it.  

I guess I have to go back to the imported excel data (pivot table results) to investigate further.  But I just wanted to ask if there is any way to control the data type when importing an excel sheet.  I know that sometimes columns import as text/number occasionally turn out as dropdowns when I look under properties.  The first tip off to this is when you start to see multiple entries for a column in the report builder, one for each data type. 

Thanks of your feedback!




  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    There is no way to force the import to a certain data type. It does not matter* what you do on the Excel side.

    Worse, if the data changes even slightly, Smartsheet's importer may make a different decision on what the column type should be. See my bug report here for an example:



    Back to your post, I am confused - are you adding the formula to the imported sheet? The ACCOUNTS sheet has the data and someone entered 123456?

    You can usually tell if the data is a number (right justified) or text (left justified)

    I would change VLOOKUP() to INDEX(...,MATCH(...) for either of the following:

    Option #1: Excel import always gets a text value.

    1. Add a new column on the ACCOUNTS sheet and use this formula:

    =whatever-your-number-column-is-called@row + ""

    This will force the value to text.

    Change the formula to look for this value instead of the original one. Text == Text. Bob's your uncle.

    Option #2: Sometimes Excel imports as text, sometimes as number, maybe in the same import.

    1. Wrap the formula in an IFERROR(your formula here, a different version of the formula)

    where the difference is one expects the value to be Text, the other expects it to be a Number. If it still doesn't find it, you may need to wrap the second formula in IFERROR() too.

    I hope that is not too confusing and helps.




  • Mike L.
    Mike L. ✭✭✭

    This is very helpful. Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!