I titled this blog “Voodoo Lookups” because that’s what people often think of when they are asked about the wonderful VLOOKUP function. It’s black magic, complex, and confusing. My goal here is to debunk the voodoo and show you how this function works, because it’s a powerful tool for manipulating Salesforce data quickly.
My assumption with this post is that you have familiarity with importing and exporting data from Salesforce with a data loading tool and understand database relationships and how to populate lookup fields on an import (by populating with a Salesforce ID).
A good use case for the VLOOKUP function is to relate data when there is not an External ID field to work with. This is common on data import situations or when taking a flat file and having to split it into multiple objects through relationships. Let’s take a hypothetical Salesforce org that has a list of “Technicians” and a list of “Jobs” on two custom objects. I’m starting off with a flat file with all details. One Technician can have multiple jobs assigned. My unique is the Technician’s name that I will be using to associate and VLOOKUP on.
Preparations
Source file looks like this:
Create a deduped file of Technicians (see previous blog post here) which would look like this:
Import Parent Object (Technicians)
Import Technicians & get results file. Most import routines will return a success file. The success files add a column with a Salesforce ID to the table, and usually a note about the success.
VLOOKUP Magic on Child Object
VLOOKUP will allow us to match each Job record to the proper Technician ID from the success file. The VLOOKUP function has 4 parts.
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
- lookup_value – The Lookup Value is the value on your spreadsheet you want to compare from. In this case, it’s the Technician’s name on the Jobs object. Since we want to compare that Technician’s name to the inserted Technician’s name records and return a Salesforce ID.
- table_array – The table_array is where all the data lies that you want to compare with. In this case, we’d want to include the technician’s name & salesforce ID in that table_array.
- col_index_num – This parameter defines which column in the table_array contains the return value. It is a number, if your array is 5 columns wide, and the return value is in the 5th column, you’d put a 5 here.
- range_lookup – This tells Excel whether to use fuzzy logic or strict logic while looking for a match. The values here are TRUE and FALSE. I always use FALSE when dealing with Salesforce data as I’m always looking for an exact match. (FALSE = EXACT)
So in the case of this data load, my formula will look like this:
=VLOOKUP(A2,[Workbook4]Sheet1!$A$2:$B$6,2,FALSE)
A2 Is my lookup reference (want to match the technician name), The [Workbook4]… is the bounding area of the source data, column 2 from my technician source file is the result that I want returned, and FALSE enforces an exact match. (Note that in the success file, I moved the technician name to the left column since the VLOOKUP function moves left to right and I wanted to pull the ID as my result value)
When applied, and filled down the column, the formula will match every Job from the matching name record and populate the corresponding Salesforce ID. The result will look like this (Click to see Full Resolution Version):
Another Great Use Case
VLookups allow you to compare Excel fields and populate in a related value. Which is very useful in relating data for import purposes, but could also be used in other cases such as cleaning up picklist values. To clean up picklist values, create a file with ALL of the unique picklist values, and then “tag” each value with what it should be in a 2nd column. Create a VLOOKUP column in the source file and use it to fill all of the picklist values in with the correct value & import.
In Review
VLOOKUPS are a useful tool for relating field values in Excel to create relationships for import purposes or clean picklists for data cleanup operations. The learning curve for the formula is a little steep, but hopefully breaking it down here will help get past it so you can start using VLOOKUPS today, solve your organization’s challenges, and be a hero! Curious, do you have any other great use cases for the VLOOKUP function? Share it in the comments, thanks! This concludes my two part series with some neat Excel tricks!
