The Most Powerful Lookup Function For Accountants
By Founder of Backbone: Ng Xian Hui
Originally posted on Chartered Accountants Lab (ISCA) at https://ca-lab.isca.org.sg/technicalities/xlookup-or-vlookup/
TAKEAWAYS
- XLOOKUP is a relatively new function in Excel, available in Microsoft 365, Excel 2021, and the web version. Compared to VLOOKUP, it requires fewer keystrokes and offers more precise control over its behaviour.
- While XLOOKUP is more efficient than VLOOKUP in many ways, it is crucial to consider its compatibility, such as with the versions of software currently being used by colleagues and clients, and also its familiarity to those using it.
We accountants are big fans of lookup functions. Outside of accounting software, we handle and process financial data that move from one worksheet to another before finally appearing in financial reports. Lookup functions are crucial because they prevent the multiple entry of the same data, which boosts both accuracy and efficiency.
But which lookup function is the best in the accounting world? And are you using it to its full potential? Let’s explore and compare.
THE SYNTAX
XLOOKUP is a relatively new function in Excel, available in Microsoft 365, Excel 2021, and the web version. Here’s how you write it:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
On the other hand, here’s the syntax for the older VLOOKUP:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
XLOOKUP allows for more parameters (six) compared to VLOOKUP (four), but both require three mandatory arguments.
THE DIFFERENCES
Lookup & return array vs Table array & column index
When using VLOOKUP, we select the lookup table by referring to the entire table (table_array) and specify the column from which Excel should return the value (col_index_num). However, there are several shortcomings to this approach:
- The lookup array (where we want Excel to search for the key) must always be in the leftmost column of the referenced table. If the lookup array is positioned to the right of the return array, then we need to restructure the table. Repeating this process can cost us a few extra minutes each time.
- If we insert a column between the lookup column and the return array after the function has been written, the return array will remain as the x-th column because the col_index_num does not automatically adjust. This will result in an incorrect output.
- It’s also worth mentioning that I hate counting columns!
XLOOKUP addresses these issues by requesting the lookup array and return array as separate arguments. This flexibility means that the lookup array does not need to be the leftmost column in a table. Additionally, because each array is selected by specifying the exact columns, any changes such as inserting new columns between them or moving the columns around won’t disrupt the function.
While it’s a less common scenario, the two arrays in XLOOKUP don’t even need to be on the same worksheet.
XLOOKUP is often touted as a replacement for VLOOKUP, but it isn’t a complete substitute in every accounting context. For example, when dealing with two-dimensional lookups, such as retrieving an account balance by month, VLOOKUP remains the better option due to its straightforward approach to handling these data structures.
While XLOOKUP can handle similar tasks using the INDIRECT or OFFSET functions, it’s important to consider that both INDIRECT and OFFSET are volatile functions. Volatile functions recalculate more frequently than a normal function, for example, the MATCH function, which is often used with VLOOKUP in two-dimensional lookup scenario. This frequent recalculation can potentially slow down the performance of your Excel workbooks, an important factor to consider when working with large and complex spreadsheets.
If entry not found
When performing lookups, we might not always expect to find a matching entry. Instead of Excel returning an error like #N/A – which can cause dependent calculations to fail – it’s often more useful for it to display an empty cell, a zero , or a custom error message like “Account not found!” With VLOOKUP, achieving this requires nesting the function inside IFERROR to handle errors gracefully.
On the other hand, XLOOKUP simplifies this process considerably. It includes a dedicated argument, if_not_found, that directly allows you to specify what should be returned if no match is found. This feature eliminates the need for additional functions like IFERROR, reducing the complexity of your formulas.
Match mode or range lookup
The last argument of VLOOKUP is optional but in the accounting context, it usually needs to be set to FALSE or 0. This specifies an exact match, which is essential for most accounting tasks. If this argument is omitted, Excel defaults to TRUE, indicating an approximate match, which is typically not suitable for our use cases. This makes the last optional argument of VLOOKUP effectively mandatory.
On the other hand, XLOOKUP’s match mode offers four choices, with the default setting being an exact match. This default setting makes XLOOKUP more efficient for accounting purposes because it allows you to skip specifying this argument, reducing keystrokes and simplifying formula writing.
It’s lesser known, but VLOOKUP can actually work with wildcards like * and ?. While useful in some scenarios, having wildcards in your lookup array can complicate things. XLOOKUP, however, disables wildcard lookups by default, providing the flexibility to enable them only if needed.
I will skip over search_mode, as it is not commonly used in accounting contexts.
Compatibility
Before you rush to replace all your VLOOKUP formulas with XLOOKUP, consider a crucial factor: compatibility. XLOOKUP is only available in the newer versions of Excel, such as Microsoft 365 and Excel 2021. If your colleagues, boss, or clients are still using older versions of Excel, they won’t be able to use spreadsheets that incorporate XLOOKUP.
Additionally, ensure that your co-workers are familiar with XLOOKUP before you make the switch. This helps prevent confusion and streamlines the transition process within your team.
VERDICT
From our comparison, it’s evident that XLOOKUP is a superior choice for accountants – it requires fewer keystrokes and offers more precise control over its behaviour. Therefore, unless you’re dealing with two-dimensional lookups, which might still favour VLOOKUP for simplicity, it’s worth considering a move to XLOOKUP.
