The VLOOKUP, Custom Report Types, and Joined Reports
How To Use a VLOOKUP
For me, learning how to use the VLOOKUP function in Excel was something I put off learning because it seemed confusing. However, now that I have learned how to use VLOOKUP, it's an easy, powerful tool for combining data sets that are linked by an ID or name.
To start with, let’s walk through the first use case I encountered to understand what a VLOOKUP can accomplish.
At my company, we were hosting a security lunch and learn. We wanted to send a special invitation to primary points of contact we had at companies who were paying for our IT security services.
We were able to export a list of companies using security services, and we were able to export a list of all primary contact from any company along with the company name.
In this case, the contacts and account were exported from the same system, so account name was an exact match between the contacts and account.
Note: this system was not Salesforce.
Now, if you're the type of person who likes detailed written instructions, I'd encourage you to jump over to Microsoft's official post.
Here's a video of how to user a VLOOKUP in this scenario:
When should you use a VLOOKUP as a Salesforce Admin?
If you've been working with Salesforce for a little while, you are probably asking why you would want or need to use a VLOOKUP instead of a custom report type or a joined report.
Custom Report Type Limitations
To help visualize the structural limitations of a custom report, I like to imagine a downward arrow.
There will always be a primary object at the top as a starting point.
From the primary object, you can go down the data relationship tree selecting only one object that looks up to the primary object. However, from any object, you can also pull in fields from any object related through a lookup field.
To give some context to this illustration, see these examples:
This ERD could be built in a custom report type with lookup fields added to the page layout:
This ERD cannot be built in a custom report type:
The above ERD could be built in a joined report joining on Account ID, but joined accounts do have some limitations noted below.
Joined Report LIMITATIONS
Functionally, joined report work very similarly to a VLOOKUP joining two objects on a common identifier instead of through a relationship field.
Joined reports have a few primary and straightforward limitations:
"Reports display a maximum of 2,000 rows. To view all the rows, export the report to Excel or use the printable view for tabular and summary reports. For joined reports, export is not available, and the printable view displays a maximum of 20,000 rows." https://help.salesforce.com/articleView?id=rd_reports_limits.htm&type=5
What about INDEX MATCH?
Users proficient in Excel will quickly ask what about INDEX MATCH? It's a more powerful formula combination that can do everything a VLOOKUP can do and more. While I have no doubt that a future blog post will cover INDEX MATCH in more detail, for now it is worth noting two advantages that a VLOOKUP has over an INDEX MATCH.
Easy of Use
The syntax of a VLOOKUP is easier to understand and remember.
VLOOKUP is less CPU intensive
VLOOKUP is less CPU intensive than an INDEX MATCH. This is especially impactful when running the lookup with 100k+ record.