In honor of my favorite holiday, Halloween, I’d like to share my horrifying Salesforce story that had me in a cold sweat! Vlookup matching to 15 characters IDs instead of 18…
Oh, I remember the day so well. I’m fresh into my new job as a Jr. Salesforce Administrator and I am beginning to learn about data matching and manipulation in Excel. I’m listening, writing, and missing half of the instructions because I’m busy trying to write detailed notes that’d make my grandmama proud!
There I am hovering, barely listening, and questioning my life choices and then my boss asks if I caught all that. Well, I did what any timid guy does: I said, “YUP!” and I walked away to my desk with a horrified look on my face. At this point, my boss freed me from the shackles of working only in the sandbox and allowed me to touch our production org, great, right? Well. Well. Ugh.
I was tasked with creating a report of all of our active Client Accounts so I could pull the Account IDs to then match them to IDs on an excel file, simple enough! I’m creating the report, exporting as CSV then I combine both files into one to do a simple Vlookup. I’m feeling quite comfortable using the Vlookup function even though I’ve only used it a handful of times. I knew that the formula was working because it was returning values instead of the dreaded #N/A! Success!
As I’m analyzing the data, I get a feeling that it looks off because some of the dollar values kept repeating in other rows, even down to the cent. I’m sitting there and thinking, “Well, maybe these four clients just happen to pay the same price of $4654.87… I should not question the omniscient formula” so I continue on. Scrolling down, I kept noticing sets of rows that have duplicate values but at this point, I am 37% convinced that I did it right which was an improvement from the -1% when we first started! The light at the end of the tunnel is finally visible and I’m spirting towards it with my completed CSV file in hand. I fire up the data loader, select to update the Account object, locate my file, map the fields and confirm the update. Boom, bam, ba-done!
I’m sitting there, staring at the command prompt screen in awe of what’s happening in the background and then as this process is proceeding, I have a flashback to a website that my boss showed me during my intense note-taking session. The website gives you the ability to transform your 15 character IDs into 18 characters. At first, this didn’t seem important because my IDs were obviously unique so I initially brushed it off. But then I wondered, “are Vlookups case-sensitive?” I searched that very question and then I saw it; Vlookup formulas are case-insensitive. My stomach was in knots, I felt a cold sweat, and I was horrified at what was happening. Suddenly, watching the command prompt scared me more than most horror films.
The upload finished but my mind was racing faster than Florence Delorez Griffith Joyner in 1988. I couldn’t believe that this was happening, but I also wasn’t ready to give up. I quickly mapped out my new game plan: convert both of the IDs to 18 characters and update the new results into Salesforce! I’ve never felt myself move so fast! Thankfully, the problem was quickly rectified, and nobody ever noticed it! Heh! Be careful out there before you get trick-o-fired!