XLOOKUP Function

Summary

The Excel XLOOKUP function searches one range for a match and returns a corresponding value from another range. It is more flexible than VLOOKUP and HLOOKUP because the lookup range and return range are separate, so the return value does not have to be on one particular side of the table.

XLOOKUP is often used as a modern replacement for older lookup formulas. It supports exact match by default, has built-in handling for missing results, can search from the bottom of a list, and can return multiple columns at once.

Because of that flexibility, XLOOKUP is useful in both simple lookups and more advanced models where table structure may change over time.

Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

lookup_value is the item to find, lookup_array is where Excel searches for it, and return_array is what Excel returns when it finds a match. The remaining arguments let you control missing matches, match behavior, and search direction.

XLOOKUP uses exact match by default, which is one of the main differences from older lookup functions that often rely on approximate match unless you specify otherwise.

Arguments

• lookup_value - The value to search for.
• lookup_array - The range or array where the match should be found.
• return_array - The range or array to return a result from.
• if_not_found - [Optional] The result to return if no match exists.
• match_mode - [Optional] 0 exact match, -1 exact or next smaller, 1 exact or next larger, 2 wildcard match.
• search_mode - [Optional] 1 first-to-last, -1 last-to-first, 2 binary ascending, -2 binary descending.

The lookup array and return array should line up in size. If one has more rows or columns than the other, the result will not behave as intended.

XLOOKUP vs Other Functions

XLOOKUP overlaps with several older lookup functions, but its main advantage is flexibility.

In many modern workbooks, XLOOKUP can replace older lookup formulas with one function. In older or shared files, you may still see VLOOKUP, HLOOKUP, or INDEX with MATCH instead.

Using XLOOKUP

XLOOKUP is especially useful because it handles several common lookup problems directly. It can look to the left, return a custom message when no match exists, and search from the bottom of a list when the most recent match matters.

It is also useful for returning multiple columns in one step. If the return array includes several columns, the result can spill across adjacent cells and return the full matching record.

Wildcard matching is available too, but it is not automatic. If you want to use wildcards such as * or ?, set match_mode to 2.

• Use XLOOKUP when the lookup direction or return shape may vary.
• Use if_not_found to control missing matches without wrapping the formula in IFERROR.
• Use search_mode=-1 when you need the last matching item instead of the first.

Example 1 - Standard Reverse Leftward Search

This formula looks up the name "Bob" in one range and returns the ID from a different range to its left. That is a pattern that older functions such as VLOOKUP cannot handle directly.

This is a strong opening example because it shows one of XLOOKUP’s biggest advantages right away. The return column does not have to be to the right of the lookup column.

=XLOOKUP("Bob", B2:B5, A2:A5)

Example 2 - Built-in Error Resilience

Here the formula looks for a missing SKU and returns a custom message instead of an error. This is one of the most practical reasons many users prefer XLOOKUP over older lookup formulas.

That makes the sheet easier to read for non-technical users. Instead of seeing an error code, they see a normal message that explains the result more clearly.

=XLOOKUP("X-99", A2:A10, B2:B10, "Missing Item")

Example 3 - Automated Latest Transaction Logic

With search_mode=-1, XLOOKUP searches from the bottom of the list upward. That means it returns the last matching occurrence rather than the first, which is useful in running logs or status histories.

This is useful when the newest entry matters more than the oldest one. The example shows that XLOOKUP can solve that without needing to reverse the data manually.

=XLOOKUP("Update", B2:B100, C2:C100, , 0, -1)

Example 4 - Full Record Spill Retrieval

If the return array contains multiple columns, XLOOKUP can return the whole matching row in one formula. In modern Excel, that result spills into adjacent cells automatically.

This makes XLOOKUP useful for pulling back a full record, not just one field. It is a practical way to return several related values from the matched row at once.

=XLOOKUP(101, A2:A5, B2:D5)

Conclusion Recap

XLOOKUP is easier to work with than older lookup functions because it separates where you search from what you return. This lesson showed why that matters: you can look left, show your own message when nothing is found, search from the bottom for the latest match, or return a whole record at once.

That is why XLOOKUP is a strong default choice in modern Excel. If the lookup and return ranges line up properly, the formula stays clear and flexible without many of the layout limits of older methods.

• Summary: XLOOKUP searches one range and returns a matching result from another range.
• Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]).
• Core setup: Keep the lookup and return arrays aligned and use the optional arguments deliberately.
• Best use: Flexible lookups, left lookups, custom missing-result handling, and multi-column returns.