How to use VLOOKUP for case sensitive Salesforce IDs
Why does VLOOKUP sometimes match Salesforce records incorrectly?
Salesforce uses case sensitive ID numbers which can present a problem for analysis in Excel. Excel’s VLOOKUP tool is not case sensitive – i.e. it will see different Salesforce IDs as the same. For example, a Contact ID in Salesforce could be “0034J00000Q7IKsQAN” and another completely unrelated contact would have the ID number “0034J00000Q7IKsQAn” – the only difference being that the last letter is lowercase in one instance. This will mean if you use Salesforce ID numbers to do a VLOOKUP, some contacts will match with the wrong record.
What’s the Scenario?
Let’s say you have two CSV or Excel files from Salesforce and they each have an ID column. You’d like to flag on one sheet or Excel tab that the ID exists on the other, or perhaps add information from one Excel file into the other based on matching Salesforce ID.
Why is it a tricky issue?
I think the worst thing about this issue is that in most cases Salesforce IDs are different from each other regardless of case – so you may be happily using Vlookup and checking a few of the results thinking it has all worked correctly and you may not immediately realise there is an issue with the technique you’re using.
Why should this scenario be a rare occurrence?
- Salesforce reporting is excellent – you should be able to do almost all your analysis right there in Salesforce. If you don’t find the report you need, create a Custom Report type
- You can create a field which is a case insensitive ID number – do this on any Salesforce object that you might need it on, the obvious ones are Account, Contact and Opportunity. The way to do this is to create a formula field on each of the required objects using the formula “CASESAFEID (Id)”. This formula converts the 15-character case-sensitive ID to the 18-character case-insensitive ID, for better compatibility with Excel. Then when you create a report, make sure you export the case insensitive ID for the object. This is also the ID you’ll want to use for any third party integrations that may not be case sensitive.
Why might you still need an Excel solution for the Case Sensitive IDs?
- Because we’re human – maybe you’re working on a new Salesforce org and didn’t use a case insensitive formula field. You now have all the data you need in Excel and want a quick way to compare to a list of IDs
- Perhaps you’re using the Data Export option from Salesforce and your files only include the case sensitive ID – this was my scenario today when I was evaluating Attachments for a client
What’s the quick solution?
If you’ve found yourself in a situation with two files which each list the 15-digit case sensitive Salesforce IDs, then the following steps will make sure you do exact matches:
- Make sure your ID is column A (so your first data field is A2)
- Make sure columns A and B are in the ‘General’ format so you see the results not the formula
- Paste this Excel formula into B2 which will convert the 15-digit case sensitive ID into a series of numbers with a space between each:
=CODE(MID(A2,1,1))&" "&CODE(MID(A2,2,1))&" "&CODE(MID(A2,3,1))&" "&CODE(MID(A2,4,1))&" "&CODE(MID(A2,5,1))&" "&CODE(MID(A2,6,1))&" "&CODE(MID(A2,7,1))&" "&CODE(MID(A2,8,1))&" "&CODE(MID(A2,9,1))&" "&CODE(MID(A2,10,1))&" "&CODE(MID(A2,11,1))&" "&CODE(MID(A2,12,1))&" "&CODE(MID(A2,13,1))&" "&CODE(MID(A2,14,1))&" "&CODE(MID(A2,15,1))
4. Do the same in your other Excel file or tab – i.e. convert those case sensitive IDs into the series of numbers which are case insensitive
5. Copy and paste the values of your new columns
6. Complete your Excel Vlookup as normal using your new ID columns. Please double check this formula has worked for you (e.g. try a look up on a different field or in reverse to check you’re happy with the data matching)
7. This is how your data will look (although there are likely to be many more columns from column C onwards):
This blog has a helpful review of solutions for Case Sensitive columns using Excel’s VLOOKUP function.
Do you have any tips to share?
Let us know if you found this blog helpful and if you have any tips to share.