If you work with CRM systems long enough, you're going to get stuck with this situation. "Hey Ms. Admin, you know those 152,385 contacts we had you bring over from the legacy system? Well, we need you to update them with these events that we forgot about. Oh, and yeah...the update's from the old system, so all we have is the ID from there--not Salesforce's ID. Well, have a great weekend. It's 2PM on Friday, so I'm outta here!"
There you sit, dejected and defeated. The new data contains 55,811 events that have only the legacy ID's. Salesforce has 152,385 contacts with an external ID that has the legacy ID, but the system won't let you reference that for the insert/upsert. You have visions of spending the next three years manually matching and copying and pasting or trying to find the budget to hire a dozen, third world interns to do this for you. Your weekend is shot.
Wait, intrepid admin; there's hope! What you need is a beautiful thing called VLOOKUP. In a nutshell, VLOOKUP searches for a value in the first column of a spreadsheet's table array and brings back a value in the same row from different column in the table array. Let's look at a simple example.
Imagine if we knew the part numbers and wanted know what food we needed to stock in our facility to feed everything we were housing. Of course, in an example this small it's a simple matter to eyeball it, but if you have hundreds or thousands of rows, it's not so easy. In that case, you would use VLOOKUP to reference the part number and return the food, What it does is tell the database, "Here's the index value on the left most column of the spreadsheet. Go find it and tell me what's in column C, for that row."
Similarly, our harried admin would do something very similar. She would use a data tool and pull an export of the contact records in Salesforce. The only fields she needs are legacy ID and Salesforce system ID. She then places the exported data on another tab of the spreadsheet that holds the update data. It could be the same tab, but I like separate ones, just to keep it clean. Finally, she creates a VLOOKUP to bring the Salesforce ID from the exported records to the records for update. Now running the insert/upsert is no problem and she can get off work in time for dinner!
Needless to say, there's a bit more to a VLOOKUP than I presented here. Rather than reinventing the wheel, I encourage you to check out Microsoft's excellent documentation of it or to do a YouTube search for the many videos that will walk you through it, step by step.
Once you get comfortable with the VLOOKUP, take a walk on the wild side and play with HLOOKUP! :-)
