How to deal with the case insensitive VLOOKUP in Excel when preparing a Salesforce datafix?

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:

1id 7different contacts

One Salesforce contact ID has 7 different Last Names? How is that possible?!

1id 7different contacts detailed

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 ????

case insensitive vlookup

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.

trick lookup

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)



If you have another trick to do this, feel free to post it in the comments.



Table of Contents

Continue Reading

Related Posts

Industry Best Practices

Why Ignoring Industry Best Practices Could End Your Consulting Career

Learn why ignoring industry best practices in the consulting industry can have detrimental consequences for your career. From attending conferences and subscribing to industry journals to networking with peers and taking specialized courses, discover the essential steps to stay informed and relevant in your field. Don’t underestimate the importance of industry best practices – they could be the key to securing a successful and thriving consulting career.