The VLOOKUP, Custom Report Types, and Joined Reports

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.

A Scenario

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.

The Steps

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

noun_Arrow_92233_000000.png

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:

Down Arrow ERD.PNG

This ERD cannot be built in a custom report type:

Split ERD.PNG

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.  

 

Salesforce Lightning Account Contact Roles

Salesforce Lightning Account Contact Roles

What is KPI?  Why Should SFDC Admins Care?

What is KPI? Why Should SFDC Admins Care?