How to Use XLOOKUP in Excel: Simple Guide and Examples

Use the XLOOKUP function in Excel to search a range or array for a value and return a corresponding result from another range. The basic syntax is =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). It replaces older lookup functions with more flexibility and ease.

Syntax

The XLOOKUP function has six parts:

• lookup_value: The value you want to find.
• lookup_array: The range where Excel looks for the value.
• return_array: The range from which to return the matching value.
• [if_not_found] (optional): What to show if no match is found.
• [match_mode] (optional): How to match the value (exact, approximate).
• [search_mode] (optional): Direction to search (first-to-last or last-to-first).

excel

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

Example

This example finds the price of an item from a list. It looks for "Banana" in the item list and returns its price.

excel

A1:A4 contains: Apple, Banana, Cherry, Date
B1:B4 contains: 1.2, 0.5, 2.0, 3.0

Formula in C1:
=XLOOKUP("Banana", A1:A4, B1:B4, "Not found")

Output

0.5

Common Pitfalls

Common mistakes include:

• Using ranges of different sizes for lookup_array and return_array.
• Not handling cases when the lookup value is missing, causing errors.
• Confusing match_mode values, leading to unexpected matches.

Always ensure your lookup and return ranges align and use the [if_not_found] argument to avoid errors.

excel

Wrong: =XLOOKUP("Orange", A1:A4, B1:B3)
Right: =XLOOKUP("Orange", A1:A4, B1:B4, "Not found")

Quick Reference

Argument	Description	Example
lookup_value	Value to find	"Banana"
lookup_array	Where to search	A1:A4
return_array	Values to return	B1:B4
if_not_found	Value if no match	"Not found"
match_mode	Match type (0=exact, -1=exact or next smaller, 1=exact or next larger, 2=wildcard)	0
search_mode	Search direction (1=first to last, -1=last to first, 2=binary ascending, -2=binary descending)	1

Key Takeaways

XLOOKUP searches a range and returns a matching value from another range.

Always keep lookup and return ranges the same size to avoid errors.

Use the optional if_not_found argument to handle missing values gracefully.

XLOOKUP replaces older lookup functions with more flexibility and ease.

Match_mode and search_mode let you customize how Excel finds your data.