VLOOKUP data type question
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!
Comments
-
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
-
This is very helpful. Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 405 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!