Excel Agility: XLOOKUP

A two-way lookup retrieves a value at the intersection of a matching row and column—a common need in pricing tables, budget grids, or data matrices. With XLOOKUP, you can achieve this elegantly by nesting one XLOOKUP inside another. The outer XLOOKUP finds the correct row, while the inner XLOOKUP—used as the return_array argument—dynamically identifies the correct column. For example: =XLOOKUP(row_value, row_headers, XLOOKUP(col_value, column_headers, data_table)). This is far cleaner than the classic INDEX/MATCH/MATCH approach. The inner XLOOKUP evaluates first, returning a column array, and the outer XLOOKUP then searches that array for the correct row value. Both lookups benefit from XLOOKUP's flexible match modes and built-in error handling. The Excel Agility: XLOOKUP webinar from Aurora Training Advantage covers nested XLOOKUP techniques and other advanced applications of this powerful modern lookup function.

XLOOKUP supports wildcard matching through its match_mode argument. Setting match_mode to 2 enables wildcard characters: the asterisk (*) matches any sequence of characters, the question mark (?) matches any single character, and the tilde (~) escapes a literal asterisk or question mark. For example, =XLOOKUP("*Smith*", names_range, results_range,,2) finds the first entry containing Smith anywhere in the name. This is useful for partial matches when you don't know the exact spelling or need to find items containing a specific substring. Wildcard mode requires careful use—it always returns the first match and will not find exact matches that contain special characters unless you use the tilde escape. Unlike VLOOKUP, which does not natively support wildcards in the lookup value with standard syntax, XLOOKUP's explicit match_mode argument makes this behavior transparent and controllable. Aurora Training Advantage's Excel Agility: XLOOKUP webinar covers all match modes with practical demonstrations.

Yes—one of XLOOKUP's most powerful advantages over VLOOKUP is its ability to return an entire row or multiple adjacent columns in a single formula. When the return_array argument spans multiple columns, XLOOKUP spills the results across adjacent cells automatically, leveraging Excel's dynamic array engine. For example, =XLOOKUP(A2, ID_column, B:D) returns three columns of data for the matching row in one formula. This eliminates the need to write separate XLOOKUP formulas for each column or use the col_index_num workaround required with VLOOKUP. The spilled results update dynamically when the lookup value changes. This capability is particularly valuable in data retrieval forms, invoice lookups, and employee record displays where multiple fields are needed simultaneously. The Excel Agility: XLOOKUP webinar from Aurora Training Advantage demonstrates how to leverage XLOOKUP's multi-column return capability to simplify complex data retrieval workflows.

XLOOKUP's search_mode argument (the sixth parameter) controls the direction and method of the search, offering four options. Search_mode 1 (the default) searches from first to last—standard top-to-bottom for columns. Search_mode -1 searches from last to first, useful when you want the most recent match in a list with duplicate values, such as finding the latest transaction for a customer. Search_mode 2 uses a binary search on ascending-sorted data for dramatically faster performance on very large datasets. Search_mode -2 uses binary search on descending-sorted data. The reverse search (-1) is particularly practical in time-series data where entries are added chronologically and you want the last occurrence without sorting or filtering. In contrast, VLOOKUP always returns the first match with no option for reverse searching. Aurora Training Advantage's Excel Agility: XLOOKUP webinar teaches all four search modes with real-world applications so participants can choose the right approach for each scenario.

One of XLOOKUP's most practical improvements over VLOOKUP is its built-in fourth argument, if_not_found, which specifies what to display when no match is found—eliminating the need to wrap the formula in IFERROR. For example, =XLOOKUP(A2, lookup_range, return_range, "Not found") cleanly returns Not found instead of #N/A when no match exists. This keeps formulas shorter, more readable, and easier to maintain. You can use any value as the fallback—a blank string for empty cells, a zero for numerical calculations, or a descriptive message for user-facing reports. The if_not_found argument only fires for genuine no-match situations; other errors (such as wrong data types) still surface normally, unlike IFERROR which silently suppresses all error types. This targeted error handling makes XLOOKUP more precise than wrapping VLOOKUP with IFERROR. Aurora Training Advantage's Excel Agility: XLOOKUP webinar demonstrates best practices for leveraging this argument across common business lookup scenarios.