Excel VLOOKUP is one of the most ubiquitous spreadsheet functions. Seemingly, an equivalent method to a reverse VLOOKUP will be required in some cases, because a standard VLOOKUP is unable to perform a reverse lookup. This article and video will explore a couple of alternative methods to a reverse VLOOKUP.
How to create a dynamic VLOOKUP
Excel’s VLOOKUP function is undoubtedly one of the most popular Excel functions. It can readily interpret and report values in large data arrays; unlike a PivotTable, which needs to be manually updated to reflect source data changes. Often this Excel function finds it difficult to reflect changes to source data. A dynamic VLOOKUP that incorporates the COLUMN function will overcome this challenge of changing source data.
How to sum across optional column ranges using SUMPRODUCT
The Excel SUMPRODUCT function is one of the most powerful and flexible aggregation functions. This example demonstrates how SUMPRODUCT can discriminantly sum cells in either column H (“Invoice Amount”) or column I (“Amount Confirmed”), based on whether there is a corresponding amount in column I.
Excel INDEX MATCH for two dimension lookups
The VLOOKUP function is a highly useful Excel function in financial analysis and business planning. It is a valuable one-dimension lookup tool – along with HLOOKUP. However in some cases, a financial analyst will require a two-dimension lookup tool, in order to reference the exact value from a data table, which a vanilla VLOOKUP function will be unable to perform.
The combination of the INDEX function with two MATCH functions can solve this matter.
Excel VLOOKUP across multiple worksheets
A standard Excel VLOOKUP can refer to values from a specific worksheet. Financial and business planning models are often complex and detailed in their analytics, and therefore require high-level executive summaries or dashboards of multiple worksheets.
The following illustration demonstrates the power of a VLOOKUP with the INDIRECT function to reference a Lookup_value from multiple worksheets.
How to do a VLOOKUP – A Look into the basics
Excel’s VLOOKUP function is undoubtedly one of the most popular Excel functions. It can readily interpret and report values in large data arrays and unlike a PivotTable, which needs to be manually updated to reflect source data changes; VLOOKUP will seamlessly update to reflect such changes.