Excel Agility: Lookup Functions Part 1

VLOOKUP searches for a value in the first column of a table and returns a value from a specified column in the same row. Its syntax is =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup). Using FALSE as the last argument performs an exact match, which is almost always what accounting and finance professionals need. VLOOKUP is widely used to pull account descriptions, prices, or employee data from reference tables into working spreadsheets. However, it has key limitations: it can only look to the right—the lookup column must be the leftmost column in the range. Inserting or deleting columns shifts the col_index_num, potentially breaking formulas silently. It also returns only the first match, which can cause issues with non-unique lookup values. For accounting professionals who need more flexible lookups—searching in any direction or returning based on multiple criteria—INDEX/MATCH or XLOOKUP are superior alternatives. Aurora Training Advantage's Excel Agility: Lookup Functions series provides structured guidance on mastering all Excel lookup techniques.

INDEX/MATCH is a powerful two-function combination that overcomes VLOOKUP's limitations. MATCH finds the position of a value within a range, and INDEX uses that position to return the corresponding value from any column or row. The combination =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) performs the same basic operation as VLOOKUP but without the column-order restriction—you can look up and return values in any direction. INDEX/MATCH is also more resilient to structural changes: inserting or deleting columns does not break the formula because you reference the return range directly rather than by column number. It also handles two-way lookups elegantly when used with two MATCH functions for both row and column positions. For accountants building financial models, reconciliation tools, or reporting templates that are frequently modified, INDEX/MATCH is the preferred lookup approach. It remains relevant even in Excel versions that have XLOOKUP, as it is available in all Excel versions from 2003 onward.

XLOOKUP, introduced in Excel 2019 and Microsoft 365, is designed as a modern replacement for both VLOOKUP and HLOOKUP. Its syntax is =XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode). Unlike VLOOKUP, XLOOKUP is not restricted to returning values to the right of the lookup column—it can look left, right, up, or down. It natively handles the not-found case with a built-in fourth argument, eliminating the need to wrap it in IFERROR. XLOOKUP also returns an array by default, meaning it can retrieve multiple columns at once. It supports approximate match, exact match, wildcard match, and binary search modes through the match_mode argument. For accounting professionals building reconciliation tools, chart of accounts lookups, or multi-column data pulls, XLOOKUP is significantly faster to write and easier to maintain than VLOOKUP. The primary limitation is that it is not available in Excel 2016 or earlier, making INDEX/MATCH still relevant for organizations on older versions.

A two-way lookup retrieves the value at the intersection of a specific row and column in a table—for example, finding the sales figure for a particular product in a particular month. The classic approach uses INDEX with two MATCH functions: =INDEX(table, MATCH(row_value, row_labels, 0), MATCH(col_value, col_labels, 0)). The first MATCH locates the row position; the second locates the column position; INDEX returns the value at that intersection. This technique is extremely powerful for financial models with matrix structures, such as budget-vs-actual tables, pricing grids, or commission schedules. XLOOKUP can also perform two-way lookups by nesting one XLOOKUP inside another as the return_array argument. Both approaches are more flexible and reliable than manually hard-coding row and column offsets. For accounting and finance professionals who work with structured financial data tables, mastering two-way lookups eliminates manual cell referencing and makes models significantly more dynamic and maintainable.

Excel lookup functions offer two primary matching modes: exact match and approximate match. Exact match—specified by using FALSE or 0 in VLOOKUP/MATCH—finds only a value that precisely equals the lookup value, returning an error if no match exists. This is the correct mode for virtually all accounting lookups such as account numbers, employee IDs, or product codes where precision is mandatory. Approximate match—specified by TRUE or 1—finds the largest value that is less than or equal to the lookup value, and requires the lookup column to be sorted in ascending order. It is used for range-based lookups such as tax bracket calculations, tiered commission rates, or shipping cost schedules where you want to find which band a value falls into. Using approximate match on unsorted data or for exact-value lookups is a common error that returns incorrect results silently, making it critical to understand which mode applies to each situation. Aurora Training Advantage's Excel Agility lookup training covers both modes with practical accounting examples.