This follow-up article on the use of Excel AGGREGATE SUM introduces the added complexity of error cells, text cells or other erroneous cells that would cause errors with a standard Excel SUM function.
Excel AGGREGATE, not Excel aggravate!
The simple SUM function is an adequate tool to aggregate a data array in a financial model. However there are some financial spreadsheets that will require a more robust Excel function, in order to provision for hidden rows, error values, and nested SUBTOTAL and AGGREGATE function cells.
The use of Excel AGGREGATE with SUM as the function syntax, depending on the Option selected, in terms of which values to disregard in the evaluation range for the function, will overcome certain problems with aggregating a data range.
Reverse HLOOKUP in Excel
This is a follow-up to a similar article on reverse VLOOKUP options. There will be instances where a reverse HLOOKUP, like a VLOOKUP, is required in a financial model. The following feature will outline two alternate approaches, which enable a form of reverse HLOOKUP.
Reverse VLOOKUP – Equivalent methods
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.
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.