XLOOKUP Function in Excel: Syntax, Arguments, and 8 Practical Examples

XLOOKUP function in Excel returning an employee name from an ID

If you still build lookups with VLOOKUP and column index numbers, the XLOOKUP function in Excel will save you time and errors. It searches one range, returns a value from another, looks to the left as easily as to the right, and has a built-in “not found” message. This guide covers the syntax, every argument, and eight practical examples you can copy.

XLOOKUP works in Excel for Microsoft 365 (Windows and Mac), Excel 2021, Excel 2024, Excel for the web, and the Excel apps for iPad, iPhone, and Android. It is not available in Excel 2019 or Excel 2016.

XLOOKUP Function in Excel: Syntax and Arguments

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
ArgumentRequired?What it does
lookup_valueYesThe value you want to find
lookup_arrayYesThe range or array to search
return_arrayYesThe range or array to return a result from
if_not_foundNoText or value returned when no match is found (otherwise #N/A)
match_modeNo0 = exact (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard
search_modeNo1 = first to last (default), -1 = last to first, 2 / -2 = binary search on ascending / descending sorted data

These argument values come from Microsoft’s official XLOOKUP documentation.

Sample Data Used in This Tutorial

All examples below use this employee list in A1:D7 and a commission tier table in F1:G5 on the same sheet.

Emp ID (A)Name (B)Department (C)Salary (D)
E101AshaSales52,000
E102RaviIT61,000
E103MeeraHR48,000
E104KaranFinance57,000
E105NehaIT66,000
E106VikramSales50,500
Min Sales (F)Commission Rate (G)
02%
50,0004%
100,0006%
200,0008%

Example 1: Basic Exact-Match Lookup

To return the name for employee ID E102:

=XLOOKUP("E102",A2:A7,B2:B7)

Result: Ravi. Unlike VLOOKUP, you do not count columns or add FALSE for an exact match, because exact match is the default. To make it dynamic, type an ID in a cell such as I2 and use =XLOOKUP(I2,A2:A7,B2:B7).

Example 2: Look Up to the Left

VLOOKUP can only return columns to the right of the lookup column. XLOOKUP does not care where the return range sits. To find the ID of an employee by name:

=XLOOKUP("Meera",B2:B7,A2:A7)

Result: E103, even though column A is to the left of column B.

Example 3: Return Several Columns at Once

Point return_array at more than one column and XLOOKUP returns the whole row segment:

=XLOOKUP("E104",A2:A7,B2:D7)

Result: Karan | Finance | 57000, spilled across three cells. Leave the cells to the right empty, otherwise you will see a #SPILL! error. Our guide on how to fix the #SPILL! error in Excel explains each cause.

Example 4: Show a Friendly Message When Nothing Matches

=XLOOKUP("E110",A2:A7,B2:B7,"Not found")

Result: Not found instead of #N/A. This replaces the old IFERROR(VLOOKUP(...),"Not found") pattern, and it only catches missing matches, not other formula mistakes.

Example 5: Approximate Match for Tiers and Brackets

To find the commission rate for sales of 125,000, use match_mode -1 (exact match or next smaller item):

=XLOOKUP(125000,F2:F5,G2:G5,,-1)

Result: 6%, because 100,000 is the largest threshold that does not exceed 125,000. A sale of 49,999 returns 2%, and 200,000 returns 8%. With XLOOKUP the tier table does not need to be sorted for match_mode -1 or 1; only the binary search modes (2 and -2) need sorted data.

Example 6: Wildcard Lookup

Set match_mode to 2 to use * (any characters), ? (one character), and ~ (escape):

=XLOOKUP("Ka*",B2:B7,C2:C7,"No match",2)

Result: Finance, the department of the first name that starts with “Ka”. Without match_mode 2, XLOOKUP treats the asterisk as a literal character.

Example 7: Find the Last Match

By default XLOOKUP returns the first match from the top. Set search_mode to -1 to search from the bottom up, which is handy for the latest entry in a log:

=XLOOKUP("IT",C2:C7,B2:B7,,0,-1)

Result: Neha, the last IT employee in the list. The same formula without the last argument returns Ravi, the first one.

Example 8: Two-Way Lookup With Nested XLOOKUP

An inner XLOOKUP can pick the column by its header, and the outer one picks the row:

=XLOOKUP("E105",A2:A7,XLOOKUP("Salary",A1:D1,A2:D7))

Result: 66000. Change “Salary” to “Department” and the same formula returns IT. This replaces INDEX/MATCH/MATCH combinations.

XLOOKUP vs VLOOKUP: Key Differences

FeatureVLOOKUPXLOOKUP
Default matchApproximate (if 4th argument omitted)Exact
Look to the leftNoYes
Inserted columns break itYes (column index is fixed)No (uses ranges)
Built-in not-found messageNo (needs IFERROR)Yes
Search from the bottomNoYes
Return multiple columnsOne formula per columnOne formula, spills
Works in Excel 2019 and earlierYesNo

If you are new to lookup formulas, start with our list of 10 essential Excel formulas, then come back to XLOOKUP.

Common XLOOKUP Errors and Fixes

ErrorLikely causeFix
#N/ANo match and no if_not_found value; extra spaces or numbers stored as textAdd if_not_found, wrap the lookup value in TRIM, or convert text-numbers
#VALUE!lookup_array and return_array have different sizesMake both ranges cover the same rows, e.g. A2:A7 and B2:B7
#NAME?The Excel version does not support XLOOKUP, or the name is misspelledUse Excel 2021 or later, or INDEX/MATCH in older versions
#SPILL!A multi-column result is blocked by dataClear the cells to the right of the formula

XLOOKUP in Google Sheets and LibreOffice Calc

The same syntax works outside Excel. Google Sheets supports XLOOKUP with the same six arguments, and LibreOffice Calc added XLOOKUP in version 24.8. Workbooks that use it can move between the three apps without rewriting the formulas.

FAQ: XLOOKUP Function in Excel

Is XLOOKUP better than VLOOKUP?

For most new work, yes. It defaults to exact match, can look left, survives inserted columns, and handles missing values. Keep VLOOKUP only when files must open in Excel 2019 or earlier.

Can XLOOKUP return more than one match?

XLOOKUP returns the first (or last) match only. To return every matching row, use the FILTER function, for example =FILTER(B2:B7,C2:C7="IT").

Does XLOOKUP need sorted data?

No. Only search_mode 2 or -2 (binary search) requires sorted data. Exact, approximate, and wildcard matches work on unsorted lists.

Is XLOOKUP case-sensitive?

No. “e102” and “E102” match the same row. For a case-sensitive lookup, combine XLOOKUP with EXACT: =XLOOKUP(TRUE,EXACT(A2:A7,"E102"),B2:B7).

Practice This XLOOKUP Tutorial

Download the companion practice workbook below the article. It contains the same employee and tier data with empty answer cells for all eight examples, plus a Solutions sheet with the working formulas.

Download the practice workbook

The same data as this tutorial, with empty answer cells and a Solutions sheet.

Team Xl in Excel

Team Xl in Excel

Spreadsheet Architect & Educator at Xl in Excel. Sharing advanced formulas, dynamic arrays, and clean data modeling techniques.

Learn More About Team Xl in Excel →

Leave a Reply

Your email address will not be published. Required fields are marked *

Zoom: 100% | UTF-8 | Xl in Excel