Exploring Different Solutions
By Founder of Backbone: Ng Xian Hui
Originally posted on Chartered Accountants Lab (ISCA)
TAKEAWAYS
- In Excel, most lookup functions are designed to accept only a single lookup criterion. But in accounting, scenarios frequently arise where multiple criteria are essential.
- The workaround approach allows only one argument for the lookup criteria. To perform a multi-criteria lookup, a common approach is to combine multiple criteria into a single identifier, thus creating a unique value that represents all necessary criteria.
- Another approach is to use functions that support multiple criteria by design, for example, SUMIFS and FILTER.
Lookup is a common scenario in our daily lives, seamlessly integrating into various activities to simplify and streamline processes. Take, for example, the experience of checking out a shopping basket in a supermarket: each product’s barcode, acting as the lookup criterion, is scanned at the counter. While a barcode itself does not carry extensive details, it serves as a unique identifier that triggers a lookup in the store’s database (lookup table) to retrieve specific information, such as product description and unit price (values). This method is incredibly useful as it eliminates the need to manually enter product details, reducing data entry time and minimising human error. Moreover, barcodes ensure consistency across transactions, as each scan retrieves standardised data like product name, price, and inventory levels.
In Excel, most lookup functions are designed to accept only a single lookup criterion. However, in accounting, scenarios frequently arise where multiple criteria are essential. For instance, when looking up the budgeted amount for a specific expense category within a particular cost centre, both the general ledger account code and the cost centre serve as necessary lookup criteria. In this article, we will explore effective methods to perform multi-criteria lookups in Excel.
THE WORKAROUND APPROACH
Take a look at the syntax of accountants’ favourite function, VLOOKUP:
=VLOOKUP (lookup_criteria, lookup_table, column_index, [approximate_or_exact])
In this function, only one argument is available for the lookup criteria. The same limitation applies to other popular functions like MATCH, LOOKUP, HLOOKUP, and even XLOOKUP.
To perform a multi-criteria lookup, a common approach is to combine multiple criteria into a single identifier, essentially creating a unique value that represents all necessary criteria. For example, instead of searching by first name and last name separately, you could combine them into a single full name and perform a lookup based on the full name.
Similarly, in an accounting context, if you need to look up data based on both account code and cost centre, you could create a unique identifier by joining these two (or more) fields together and using it as your lookup criterion. Here’s how you can create a unique identifier by concatenating values in columns B and C with a hyphen as a delimiter: =B3&”-“&C3.
In the lookup function, we will do the same thing by concatenating the lookup criteria using a hyphen as a delimiter.
Creating a helper column (Column A) may not be efficient in some situations, as it adds an extra step to apply formulas to new entries in the lookup table. If you are using the latest version of Excel, which includes XLOOKUP, you can perform the concatenation of columns directly within the XLOOKUP function. This is because of XLOOKUP’s ability to handle dynamic arrays, allowing for more flexible and powerful lookups. To learn more about dynamic arrays, refer to my previous article, “Dynamic Array Formulas In Excel”.
=XLOOKUP(B10&”-“&C10,B3:B5&”-“&C3:C5,D3:D5)
This workaround approach relies on creating a unique identifier by combining multiple criteria into a single value. However, selecting an appropriate delimiter is crucial to ensure accuracy. The delimiter you choose – whether it’s a hyphen, underscore, or another character – should be distinctive enough to prevent any overlap with the actual data in each column. For example, if one of your criteria contains hyphens as part of its normal data format, using a hyphen as a delimiter could lead to confusing or incorrect matches.
FUNCTIONS THAT ACCEPT MULTIPLE CRITERIA BY DESIGN
Another approach to do multi-criteria lookup is using functions that support multiple criteria by design. Two examples that we will explore here are SUMIFS and FILTER. These functions are more reliable and more efficient for multi-criteria context here. But each of them has some other limitations.
SUMIFS is another popular function used by accountants. It sums up the values for entries in the lookup tables that meet the criterion or criteria. The syntax is as follows:
=SUMIFS(value_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
The main limitation of SUMIFS is that it only returns numerical values. Additionally, if multiple occurrences of the criteria exist in the lookup table, SUMIFS will add all matching values together, which may not be suitable in cases where you need a single, distinct match rather than a cumulative total.
The FILTER function in the latest version of Excel is more flexible, allowing you to perform a range of operations beyond simple lookups. With FILTER, you can not only apply multiple criteria but also more complex conditions to filter and retrieve specific data sets dynamically. However, this added capability also introduces complexity.
Unlike more familiar functions that follow a straightforward syntax, FILTER requires a different approach for combining criteria. For example, using multiplication (*) for AND conditions and addition (+) for OR conditions within a formula can be confusing initially, especially for users accustomed to simpler functions. Additionally, as you add more layers of criteria, the formula can become more complex to manage and troubleshoot. While FILTER can handle sophisticated operations, its syntax may feel overwhelming for those new to dynamic arrays or advanced filtering techniques.
The FILTER syntax for multi-criteria scenarios is as follows:
=FILTER(value_range, (criteria_range 1 = criteria1) * (criteria_range2 = criteria2), [if_empty])
One key behaviour of the FILTER function to be aware of is its ability to return multiple matching entries. When multiple results meet the specified criteria, FILTER will return all of them, automatically spilling into adjacent rows or columns as needed. This dynamic array feature is powerful for generating lists of matches. However, if you’re expecting only one result, the spilling behaviour may require extra management to prevent overlapping with other data on your sheet.
CONCLUSION
So, which one is better? There is no clear winner. However, in an accounting context, I would think SUMIFS would likely be the most appropriate function when we work with numerical data.
I summarise the possible solutions as follows:
