Excel Tips

Tips From the Finance Trenches: Speed Up Your VLOOKUP Formulas With Automation

As finance professionals well know, the VLOOKUP formula is among the most commonly used function in Microsoft Excel. This relatively simple-but-powerful formula helps you create analysis, summaries, models, and other key reporting elements in your worksheets.

However, while the VLOOKUP function is fairly easy to use, Excel users spend a considerable amount of time on small tasks like making sure their formulas are pointing to the right data, in the right column.

So here’s the good news: you can save yourself all that time by simply automating your VLOOKUP formula. Here we’ll lay out some simple illustrated examples to bring you up to speed. 

Example 1

vlookup.png

Referencing Example 1, let’s say we would like to pull March revenue for customer #1002. We would use the VLOOKUP formula as shown in cell D12 with col-index-num equal to 4.

Now imagine you would now like to pull May revenue for the same customer. You would have to manually change the col-index-num from 4 to 5. Changing this number every time for large spreadsheets and models is time-consuming, manual, and therefore prone to error. The power of using the MATCH function in tandem with VLOOKUP helps overcome this shortcoming, as we will demonstrate in Example 2.

Example 2

vlookup2.png

Example 2 shows a second VLOOKUP formula in cell D13; with col-index-num 4 substituted with MATCH($B$11,$A$3:$G$3,0). Here are the steps to create the MATCH function: 

Step 1. Type MATCH, followed by an open parenthesis. For example:

  • VLOOKUP($A13,$A$3:$G$9,MATCH( 

Step 2. Select the lookup value, followed by a comma. In this case, the lookup value is B11, or Mar. You can choose to type the name in the formula instead of referring to a cell number; but make sure to have that value (“Mar”) in quotation marks if you do so. Otherwise, the syntax should be as follows:

  • VLOOKUP($A13,$A$3:$G$9,MATCH($B$11, 

Step 3. Select the lookup array, or the row of the column heading. In Example 2, the lookup array is $A$3:$G$3. Make sure the range is similar to the VLOOKUP range. In our example, we select cells A:G in the VLOOKUP; thus the MATCH formula must have the same range of A:G. Finish the MATCH formula with a comma and a closed parenthesis, as follows:

  • VLOOKUP($A13,$A$3:$G$9,MATCH($B$11,$A$3:$G$3,0) 

Step 4. Finally, complete the VLOOKUP formula by putting a comma, zero and closed parenthesis:

  • VLOOKUP($A13,$A$3:$G$9,MATCH($B$11,$A$3:$G$3,0),0)

Now, if you change B11 to May, the VLOOKUP formula will automatically show May revenue for customer #1002. The MATCH function will always “help” the VLOOKUP formula find the reference column as long as it is in formula range.

In Summary

If you thought this Excel tip was useful, or would like to learn about the financial services that 8020 Consulting offers, don’t hesitate to contact us at any time. Our team can reengineer your complex spreadsheets, create new dynamic models, and streamline your analyses; giving you more time to focus on other high-impact initiatives for your business. 

Love Excel shortcuts? For more quick and easy Excel solutions from the trenches, be sure to check out the following:

Contact Us

Categorized in:

similar articles

Learn to think and approach problems like our financial consultants.

Financial Systems

Notes from the Field: Avoiding Surprises in ERP Stabilization Projects

This blog is based on my recent experience with a client that had implemented Microsoft Dynamics AX/Dynamics 365 for Finance and Operations. That enterprise resource planning (ERP) software offers benefits such as end-to-end system connectivity, resulting in profitability, transparency, and efficiency. It’s robust, but agile at the same time, and has a very familiar Microsoft… View Article

August 22, 2019Jackson Quach

Financial Planning & Analysis

Optimizing Your Price Increase Strategy

Price increases are a fairly controllable tactic for Finance teams looking to improve profitability. Ultimately, price increases can come in several forms (e.g., adjusting rebates or cost to serve) and may only apply to select customers. After collaborating with leadership, sales, and marketing teams to select the specific product/solution(s) for which to raise pricing, several… View Article

August 15, 2019Marco Moreno

Financial Systems

NetSuite SuiteAnalytics: Leveraging Built-In BI

While NetSuite has long been a leader in embedded analytics, Oracle clearly recognized the need for more robust reporting and analytics tools. As a proxy to and an improvement upon other third-party, plug-in solutions, Oracle developed its own solution natively embedded within its already industry-leading ERP. Netsuite SuiteAnalytics is a built-in analytics and reporting package… View Article

August 13, 2019Chris Moss

See All