Excel Tips

Tips from the Finance Trenches: A Smart Alternative to Lookup Functions in Excel

The VLOOKUP is one of the most commonly used functions in MS Excel and serves many useful purposes. But the circumstances aren’t always ideal for this function. While VLOOKUP is useful to traverse a small table for quick lookups, it is often misused in large datasets. This can lead to inefficiencies that bog down complex financial models, especially those which contain multiple calculations that execute simultaneously every time you open a file. When the VLOOKUP function is combined with intricate calculations, this issue is even more pronounced and can lead to application crashes. 

Fortunately, in these instances, there’s a solution that serves as a great alternative. Using index/match, combined with named ranges, can improve model performance and execution time, and reduce the risk associated with misaligned data. 

Here’s What to Do Instead: Use Named Ranges

It’s easy – just follow these simple steps:

  1. Select your data range
  2. Type desired name in highlighted box shown below (alternatively, this can be created through the Name Manager [Ctrl+F3])

What Are The Benefits of Named Ranges?

Benefits of using Named Ranges extend beyond the general shortcomings of lookups. Using Named Ranges is the recommended method for financial models that contain assumptions pages or proforma models with multiple input levers.

Named Ranges are also great for pivot tables where the underlying data can expand or shrink. In regards to lookups, Named Ranges allow you to select a subset of the entire table for focused lookup. This saves resources by having less data to traverse and looking only in intended area.

Another major benefit of named ranges is this: if the underlying data shifts or new rows/columns were added, the Named Range will only need to be updated once – then all of the formulas that use that Named Range will automatically pick it up. This avoids risk of a formula not having the full range of updated data set and having to update each formula one at a time.

How to Use Index/Match Instead of VLOOKUPS

Note: HLOOKUPS and other lookup functions work similarly

Use index/match to traverse the X-Y plane. For example, the following sample syntax looks up May 2017 data for Cust009  à  Index(MayData, Match(D15,CustList,0))

 

Alternate view showing the source of the named ranges.

 

Here’s the bottom line: Using Named Ranges and Index/Match has definite advantages, particularly in certain financial models. Give it a try now, then use this key function when you need it most! I hope you find this everyday tip useful. For more quick and easy Excel solutions “from the trenches,” be sure to check out the following:

Would you like to leverage more advanced capabilities in Excel that can make an immediate impact on your organization? Keep in mind that 8020 Consulting is here to help. Just click on the button below to ask us a question or learn more.

Contact Us

Categorized in:

similar articles

Learn to think and approach problems like our financial consultants.

M&A Due Diligence & Transaction Support

3 Tips for Responding to Requests in the Sell-Side Due Diligence Process

Entering a sell-side due diligence process? Here are 3 things to consider when responding to diligence requests. #1 – Understand the bigger picture. It is easy to get swept up in a sell-side process (especially one with multiple buyers) and be overwhelmed by the volume or difficulty of various data requests. We can avoid some… View Article

January 23, 2020Mitch Browne

Financial Planning & Analysis

BI & EPM Planning and Budgeting: Automation and Acceleration Tips

Budget season for companies can be extremely challenging if the budget process is manual, inefficient or not-scalable. An Enterprise Performance Management (EPM) / Business Intelligence (BI) software tool is needed in order to negate those process issues. Best practices also exist that can be adopted in the budgeting process, while using the selected tool. Let’s… View Article

January 21, 2020Vikram Mehta

Financial Reporting & Accounting

Startup Finance: Building Operations Through Two Rounds of Funding

After several years as Director of Finance at a major entertainment studio for domestic TV production and distribution, I was excited to be hired for a startup finance position – as the first finance/accounting/back-office person and the company’s 12th employee. The company eventually became a multi-platform media destination offering Olympic and lifestyle sports programming on… View Article

January 16, 2020Yasuko Furuya

See All