How to deal with the case insensitive VLOOKUP in Excel when preparing a Salesforce datafix?
- Published: Sunday, 10 April 2016 12:57
As a Salesforce administrator you are probably confronted with a lot of data files in Salesforce on a daily basis, just like me. And what #awesomeadmins ALWAYS do before import data into Salesforce is preparing the file for import!
Some of the fixes you’ll look at are:
- adding id’s to lookup fields that have to be populated
- checking if required fields contain data
- making sure currency, date, date time, phone number, email,… fields are correctly formated
- correcting values that will populate picklists
- correct any misspellings
There is a great blog post Preparing for Your Data Import: Some Useful Excel Tips and Tricks about this!
And in most cases you’ll end your fileprepping with some VLOOKUP’s, mostly to add Salesforce Id’s or data from other objects…
BUT, yes there is a but…
The gotchas with VLOOKUPS are many, so you do have to be extremely careful. A few you should be aware of:
VLOOKUPS are Case insensitive, which is a big problem if you’re looking up data based on the 15-digit Salesforce record IDs. If you’re using Salesforce IDs, always use the 18-digit IDs.
Don’t know what I’m talking about? Here’s an example:
I did a full export of all contacts (masked the names for privacy reasons, but replaced them by autonumber) based on a report from Salesforce, which obviously contains the 15-digit id. Look at what happens when I create a pivot table based on the contact id and count the last name of the records:
1 Salesforce contact id has 7 different Last Names? How is that possible?!
The reason for this is that most functions in Microsoft Excel are case-insensitive! See the last 4 characters GPAQ?
For excel writing it with uppercase ‘G’ and then lowercase ‘paq’ is exactly the same as writing it with a lowercase ‘g’ and uppercase ‘PAQ’.
So what would have happened if I wanted to add the ‘account owner name’ to all contacts and I just did it through VLOOKUP?
Yes, I know column E already contains the ‘account owner name’ (this is to show the error!). Just pretend you had another file containing contactid’s and their ‘account owner name’ for the sake of this example ????
They would all have gotten the same Account Owner Name, namely the one of the first contactid excel finds!
How can I do this correctly without having to worry about case insensitivity in excel?
The trick is using a combination of a normal LOOKUP and the EXACT function! Now it gets the correct Account Owner Name.
The EXACT function compares two text strings and returns TRUE (or 1) if they are exactly the same, FALSE (or 0) otherwise. EXACT is case-sensitive!
The normal LOOKUP function returns a value from a range (one row or one column) or from an array.
So what did we do here?
First we compare the value we want to lookup, so the contactid, to all the values in the column containing the contactid’s. This will result in only 1 TRUE (or 1) and all others FALSE (or 0). Then we do LOOKUP (return the position (row)) where you find that 1 divided by the result of our Exact function (which is 1 in case of TRUE) equals 1 and return the value in column E:E (account owner name) for that row.
= LOOKUP(1,1/EXACT (A:A, A2), E:E)
= LOOKUP (1, 1/EXACT (THE COLUMN WITH CONTACTID’S TO SEARCH IN A:A , THE CELL CONTAINING THE ID TO MATCH A2), THE COLUMN TO RETURN ITS VALUE FROM E:E)
JUST TRY IT!
If you have another trick to do this, feel free to post it in the comments.