VLOOKUP data type question

Mike L. · 09/18/18

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 · 09/18/18

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:

https://community.smartsheet.com/discussion/bug-csv-import-results-wrong-column-type

...

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.

Craig

Mike L. · 09/23/18

This is very helpful. Thank you!

VLOOKUP data type question

Comments

Help Article Resources

Categories