Excel XLOOKUP Formula Tool

The Ultimate XLOOKUP Visual Builder

XLOOKUP is a powerful Excel function that replaces VLOOKUP. Build formulas step-by-step with our interactive visual builder and master every parameter.

Build Your Formula How it Works

Learn XLOOKUP Visually

Interactive toolkit that shows you exactly how lookup arrays and return arrays work inside Excel.

excel_mock_data.csv

	A	B	C	D
1	ID	Name	Department	Email
2	101	Emma Wilson	Marketing	emma@example.com
3	102	Liam Johnson	Engineering	liam@example.com
4	103	Olivia Davis	Sales	olivia@example.com
5	104	Noah Brown	HR	noah@example.com
6	105	Ava Taylor	Engineering	ava@example.com
7	106	Ethan Moore	Marketing	ethan@example.com
8	107	Sophia White	Finance	sophia@example.com
9	108	Lucas Martin	Sales	lucas@example.com
10	109	Isabella King	Legal	isabella@example.com

ReadySheet1

Click Start to Begin

Welcome

Build Your XLOOKUP Step-by-Step

Compatible with:Microsoft Excel 365Excel 2021Google Sheets

How to Do XLOOKUP — Step by Step

Follow these four steps to write your first XLOOKUP formula in Excel

Type =XLOOKUP in a Cell

Select the cell where you want the result and start typing =XLOOKUP( to begin building your XLOOKUP formula in Excel.

=XLOOKUP(

Enter the Lookup Value

Specify what you are searching for. This can be a cell reference like A2 or a direct value. This is the core of how to do XLOOKUP in any Excel workbook.

=XLOOKUP(A2,

Define the Lookup Array

Select the column that contains the values you want to search through. The XLOOKUP function in Excel scans this range to find your lookup value.

=XLOOKUP(A2, B3:B6,

Set the Return Array

Choose the column from which the matching result should be returned. Unlike VLOOKUP, the Excel XLOOKUP return array can be to the left or right of the search range.

=XLOOKUP(A2, B3:B6, D3:D6)

XLOOKUP Function in Excel — Syntax Reference

Complete breakdown of every parameter in the Excel XLOOKUP function, as documented by Microsoft Support

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

Parameter	Required	Description	Example
`lookup_value`	Yes	The value to search for. Can be a cell reference, text, number, or logical value.	`A2 or "John"`
`lookup_array`	Yes	The range or array to search. Must be a single row or column.	`A:A or A1:A100`
`return_array`	Yes	The range or array from which to return values. Must be the same size as lookup_array.	`B:B or B1:B100`
`if_not_found`	No	Value to return if no match is found. If omitted, #N/A is returned.	`"Not Found" or ""`
`match_mode`	No	0 = Exact match (default). 1 = Exact or next smaller. -1 = Exact or next larger. 2 = Wildcard match.	`0`
`search_mode`	No	1 = First to last (default). -1 = Last to first. 2 = Binary ascending. -2 = Binary descending.	`1`

XLOOKUP vs VLOOKUP — Which Should You Use?

Wondering about VLOOKUP vs XLOOKUP in Excel? Here is why Microsoft recommends the XLOOKUP function as the modern replacement

Feature	VLOOKUP	XLOOKUP
Look left (return column before search column)
Return exact match by default
Custom not-found value
Search from last to first
Return multiple columns
Horizontal and vertical lookup
Wildcard matching
Works with unsorted data

Frequently Asked Questions About XLOOKUP

Common questions about how to use XLOOKUP in Excel and beyond

What is XLOOKUP in Excel?

XLOOKUP is a modern lookup function in Excel that searches a range or array and returns a matching item. It was introduced as a more flexible and powerful replacement for VLOOKUP, HLOOKUP, and INDEX/MATCH. The XLOOKUP function in Excel is available in Microsoft 365, Excel 2021, and Excel for the web.

How to use XLOOKUP in Excel?

To use XLOOKUP in Excel, type =XLOOKUP(lookup_value, lookup_array, return_array) in a cell. Specify the value you want to find, the range to search in, and the range to return results from. You can use our visual builder above to build the exact XLOOKUP formula you need without memorizing the syntax.

What is the difference between XLOOKUP vs VLOOKUP?

XLOOKUP is more flexible than VLOOKUP in several ways: it can search in any direction (left, right, up, or down), defaults to exact match, lets you set a custom "not found" value, and does not require column index numbers. VLOOKUP can only search the leftmost column and return results to the right. Microsoft now recommends using the XLOOKUP function instead of VLOOKUP for new spreadsheets.

Can XLOOKUP handle multiple criteria in Excel?

Yes, you can use XLOOKUP with multiple criteria by combining it with a concatenation approach. For example, =XLOOKUP(A2&B2, C:C&D:D, E:E) lets you match on two columns simultaneously. This XLOOKUP multiple criteria technique is a common pattern for advanced lookups in Excel workbooks.

Does XLOOKUP work in Google Sheets too?

Yes, XLOOKUP is also available in Google Sheets (added in August 2022). The syntax is identical, so any XLOOKUP formula you build for Excel will also work in Google Sheets. This makes XLOOKUP a universal lookup function across both major spreadsheet platforms.

What happens when XLOOKUP finds no match?

By default, the Excel XLOOKUP function returns a #N/A error when no match is found. You can customize this using the optional if_not_found parameter to display a friendly message like "Not Found" or return an empty string instead.

Can I use XLOOKUP with wildcards?

Yes. Set the match_mode parameter to 2 for wildcard matching. You can then use * (matches any sequence of characters) and ? (matches any single character) in your lookup value. For example, an XLOOKUP formula with "Jo*" would match "John", "Joseph", and so on.

Is XLOOKUP faster than VLOOKUP in Excel?

In most scenarios, Excel XLOOKUP performs similarly to VLOOKUP. However, for very large datasets, you can enable binary search mode (search_mode = 2 or -2) which is significantly faster, provided your data is sorted. This makes XLOOKUP the better choice for performance-critical workbooks.