The case insensitive vlookup in Excel is an Administrator’s nightmare. 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 importing data into Salesforce is preparing the file for import!
Some fixes you’ll look at are:
- Adding ids 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 formatted
- Correcting values that will populate picklists
- Correct any misspellings
- …
There is a great blog post called Preparing for Your Data Import: Some Useful Excel Tips and Tricks about this!
And in most cases, you’ll end your file prepping with some VLOOKUP’s, mostly to add Salesforce IDs or data from other objects…
BUT, yes, there is a but…
The gotchas with VLOOKUPS are many, so you do have to be cautious. 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:
One Salesforce contact ID has 7 different Last Names? How is that possible?!
This is because 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 an 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 contact IDs 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 the case insensitive vlookup 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 contact ID, to all the values in the column containing the contact ID’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 CONTACT ID’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.
