Case-sensitive vlookups for Salesforce in Excel 2003

I spent the last 3 hours trying to figure this out. Finally a post on the Salesforce Community forum nailed it, so here it is for my future reference and Google posterity.

Okay, some quick background…in Salesforce, every record, whether it be a donation, contact, account, connection, etc. is identified by a unique 18 character code. For example, a donation by “John and Mary Smith” may have a Donation ID of 00630000007mxP4, while Mary Smith has a Contact ID of 0033000000Kx664.

These codes are the record’s fingerprint and you can do a lot of neat stuff with your data in the background in Salesforce as long as you have that number. Namely, you can link unrelated spreadsheets together in Excel, using a simple “vlookup” formula. For example, let’s say you have a spreadsheet with a list of people that you want to import into Salesforce. It’s just first name, last name, email address, etc. You don’t want to import that list into Salesforce until you confirm whether or not they are already in there to avoid creating duplicates. If there are 100s of names, you also don’t want to check each one by hand in Salesforce….slllloooowww. Using an external data loader (I like DemandTools, personally), you can quickly export a table that contains all the existing Contact IDs along with the corresponding email address or other data that you can match up against the existing list you have. Create a blank column in the new spreadsheet and in a cell enter:

=vlookup(#,contactIDs!colA:colB,2,false)

where # is the cell that contains the email address and contactIDs!colA:colB represents the lookup range in the big contact list (let’s say the email address in ColA and the Contact ID in ColB). The “2″ is the column the formula should return as the result. So if I wanted to return a result that was in column C instead of Column B, I would say #,contactIDs!colA:colC,3,false instead.

I have been using this happily for a few weeks now, but tonight I ran into a problem. I found out the hard way that these Salesforce numbers are case sensitive. I have a donation that has an ID of 00630000007mxRY and a donation with an ID of 00630000007MxRY and while Salesforce knows they’re different, Excel thinks they’re the same and it completely skewed-up my report.

I found this page on Microsoft’s site. But it doesn’t work…after it finds that there’s an issue with case sensitivity in the lookup, it returns “no match” and not the actual result. Yeah, thanks Microsoft. I know that it’s not a match…keep looking and find the match!

Finally found the answer right here

The trick is to turn the case-sensitive ID into a non-sensitive one by adding some additional numbers at the end. The ID with a Mx will get a different set of numbers at the end than the one with MX.

Create a blank column in both spreadsheets and use this formula:

=#&CODE(MID(#,12,1))&CODE(MID(#,13,1))
&CODE(MID(#,14,1))&CODE(MID(#,15,1))

where # is the cell that contains the ID number.

Then, do the vlookup formula on this new cell as before and voila! No more issue with x and X being treated like the same thing in Excel.

tags: , , ,

About these ads

6 thoughts on “Case-sensitive vlookups for Salesforce in Excel 2003

  1. I actually ran into this *exact* same problem. I wanted to run a VLOOKUP on Salesforce IDs, figured out VLOOKUP was case-insensitive, found that *same* help page, realized they didn’t know what they were doing, and then found this.

    Great solution, thanks for the help!

  2. Hi- Just tried this and it does not work for me. I see what you are doing here, but you need a code() for each letter. For example, try your formula with these two SF ids and you will see that it does not work!

    0064000000CCec9

    0064000000CCeC9

  3. Judi – you rock!! you just totally saved me. thank you soooooo much for posting this solution.

    Roger – i just used the formula on your example and it created 2 unique numbers – not sure what’s happening on your end.

  4. You’re welcome, Laura. I have to add that an often easier solution is to install and use the Salesforce Excel Connector. With that installed, you just need a formula of =fixid(cell) and it will quickly/easily generate the right 18 digit ID. Frankly, I use this now instead of the solution I posted here. Both work though. :-)

Comments are closed.