Xlookup Formula in Excel

Introduction to Xlookup Formula

The Xlookup formula is a powerful and flexible lookup function in Excel that allows users to search for a value in a table and return a corresponding value from another column. It was introduced in Excel 2019 and later versions, replacing the Index/Match function combination and the Vlookup function. The Xlookup formula is more efficient and easier to use than its predecessors, making it a popular choice among Excel users.

Syntax and Arguments

The syntax of the Xlookup formula is as follows:

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

The arguments are:

• lookup_value: The value to search for in the lookup array.
• lookup_array: The range of cells that contains the values to search.
• return_array: The range of cells that contains the values to return.
• [if_not_found]: The value to return if the lookup value is not found.
• [match_mode]: The match type, which can be 0 (exact match), -1 (exact match from the bottom), 1 (exact match from the top), or 2 (wildcard match).
• [search_mode]: The search type, which can be 1 (vertical search), 2 (horizontal search), or 3 (search in any direction).

How to Use Xlookup Formula

To use the Xlookup formula, follow these steps:

• Enter the lookup value in a cell.
• Select the cell where you want to display the returned value.
• Type the Xlookup formula, starting with the equals sign (=).
• Enter the lookup value, lookup array, and return array arguments, separated by commas.
• Optional: Enter the if_not_found, match_mode, and search_mode arguments.
• Press Enter to execute the formula.

Examples of Xlookup Formula

Here are some examples of using the Xlookup formula:

Lookup Value	Lookup Array	Return Array	Xlookup Formula	Returned Value
Apple	A1:A5	B1:B5	=XLOOKUP(“Apple”, A1:A5, B1:B5)	Red
Banana	A1:A5	C1:C5	=XLOOKUP(“Banana”, A1:A5, C1:C5, “Not found”)	Yellow

📝 Note: The Xlookup formula is case-sensitive, so make sure to enter the lookup value and lookup array values in the correct case.

Tips and Tricks

Here are some tips and tricks to keep in mind when using the Xlookup formula:

• Use absolute references for the lookup array and return array arguments to avoid errors when copying the formula to other cells.
• Use the if_not_found argument to specify a custom value to return if the lookup value is not found.
• Use the match_mode argument to specify the match type, such as exact match or wildcard match.
• Use the search_mode argument to specify the search type, such as vertical search or horizontal search.

Xlookup vs Vlookup

The Xlookup formula is similar to the Vlookup formula, but it has some advantages:

• Xlookup is more flexible and allows for horizontal and vertical searches.
• Xlookup is more efficient and faster than Vlookup.
• Xlookup allows for wildcard matches and exact matches from the top or bottom.

To summarize, the Xlookup formula is a powerful and flexible lookup function in Excel that allows users to search for a value in a table and return a corresponding value from another column. It has several advantages over the Vlookup formula and is easier to use and more efficient. By following the examples and tips outlined in this article, you can master the Xlookup formula and improve your Excel skills.