Case-sensitive vlookups for Salesforce in Excel 2003

August 17, 2006 · Comments

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: , , ,

Related posts: