29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

Cart Login

Quick, clean, and to the point

Training Videos Functions Formulas Shortcuts Blog

Search... Search

Excel XLOOKUP Function

Summary
The Excel XLOOKUP function is a modern and flexible replacement for older functions
like VLOOKUP, HLOOKUP, and LOOKUP. XLOOKUP supports approximate and exact

https://exceljet.net/excel-functions/excel-xlookup-function 1/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

matching, wildcards (* ?) for partial matches, and lookups in vertical or horizontal

ranges.

Purpose
Lookup values in range or array

Return value
Matching value(s) from return array

Syntax
=XLOOKUP (lookup, lookup_array, return_array, [not_found], [match_mode],
[search_mode])

Arguments
lookup - The lookup value.
lookup_array - The array or range to search.
return_array - The array or range to return.
not_found - [optional] Value to return if no match found.

https://exceljet.net/excel-functions/excel-xlookup-function 2/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

match_mode - [optional] 0 = exact match (default), -1 = exact match or next smallest, 1

= exact match or next larger, 2 = wildcard match.
search_mode - [optional] 1 = search from first (default), -1 = search from last, 2 = binary
search ascending, -2 = binary search descending.

Version
Excel 365

Usage notes
XLOOKUP is a modern replacement for the VLOOKUP function. It is a flexible and
versatile function that can be used in a wide variety of situations.

XLOOKUP can find values in vertical or horizontal ranges, can perform approximate
and exact matches, and supports wildcards (* ?) for partial matches. In addition,
XLOOKUP can search data starting from the first value or the last value (see match type
and search mode details below). Compared to older functions like VLOOKUP,
HLOOKUP, and LOOKUP, XLOOKUP offers several key advantages.

Not found message

When XLOOKUP can't find a match, it returns the #N/A error, like other match
functions in Excel. Unlike the other match functions, XLOOKUP supports an optional
argument called not_found that can be used to override the #N/A error when it would
otherwise appear. Typical values for not_found might be "Not found", "No match", "No
result", etc. When providing a value for not_found, enclose the text in double quotes
("").

Note: Be careful if you supply an empty string ("") for not_found. If no match is found,
XLOOKUP will display nothing instead of #N/A. If you want to see the #N/A error when a
match isn't found, omit the argument entirely.

Match type

By default, XLOOKUP will perform an exact match. Match behavior is controlled by an

optional argument called match_type, which has the following options:

Match type Behavior

https://exceljet.net/excel-functions/excel-xlookup-function 3/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

Match type Behavior

0 (default) Exact match. Will return #N/A if no match.

-1 Exact match or next smaller item.

1 Exact match or next larger item.

2 Wildcard match (*, ?, ~)

Search mode

By default, XLOOKUP will start matching from the first data value. Search behavior is
controlled by an optional argument called search_mode, which provides the following
options:

Search mode Behavior

1 (default) Search from first value

-1 Search from last value (reverse)

2 Binary search values sorted in ascending order

-2 Binary search values sorted in descending order

Binary searches are very fast, but data must be sorted as required. If data is not sorted
properly, a binary search can return invalid results that look perfectly normal.

Example #1 - basic exact match

By default, XLOOKUP will perform an exact match. In the example below, XLOOKUP is
used to retrieve Sales based on an exact match on Movie. The formula in H5 is:

= XLOOKUP(H4,B5:B9,E5:E9)

https://exceljet.net/excel-functions/excel-xlookup-function 4/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

More detailed explanation here.

Example #2 - basic approximate match

To enable an approximate match, provide a value for the match_mode argument. In the
example below, XLOOKUP is used to calculate a discount based on quantity, which
requires an approximate match. The formula in F5 supplies -1 for match_mode to
enable approximate match with "exact match or next smallest" behavior:

= XLOOKUP(E5,B5:B9,C5:C9,, - 1)

More detailed explanation here.

Example #3 - multiple values

https://exceljet.net/excel-functions/excel-xlookup-function 5/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

XLOOKUP can return more than one value at the same time for the same match. The
example below shows how XLOOKUP can be configured to return three matchging
values with a single formula. The formula in C5 is:

= XLOOKUP(B5,B8:B15,C8:E15)

Notice the return array (C8:E15) includes 3 columns: First, Last, Department. All three
values are returned and spill into the range C5:E5.

Example #4 - two-way lookup

XLOOKUP can be used to perform a two-way lookup, by nesting one XLOOKUP inside
another. In the example below, the "inner" XLOOKUP retrieves an entire row (all values
for Glass), which is handed off to the "outer" XLOOKUP as the return array. The outer
XLOOKUP finds the appropriate group (B) and returns the corresponding value (17.25)
as the final result.

= XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))

https://exceljet.net/excel-functions/excel-xlookup-function 6/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

More details here.

Example #5 - not found message

Like other lookup functions, if XLOOKUP does not find a value, it returns the #N/A
error. To display a custom message instead of #N/A, provide a value for the optional
"not_found" argument, enclosed in double quotes (""). For example, to display
"Not found" when no matching movie is found, based on the worksheet below, use:

= XLOOKUP(H4,B5:B9,E5:E9,"Not found")

You can customize this message as you like: "No match", "Movie not found", etc.

Example #6 - complex criteria

https://exceljet.net/excel-functions/excel-xlookup-function 7/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

With the ability to handle arrays natively, XLOOKUP can be used with complex criteria.
In the example below, XLOOKUP is matching the first record where: account begins
with "x" and region is "east" and month is not April:

= XLOOKUP(1,(LEFT(B5:B16) = "x") *
(C5:C16 = "east") * NOT(MONTH(D5:D16) = 4),B5:E16)

Details: (1) simple example, (2) more complex example.

XLOOKUP benefits

XLOOKUP offers several important advantages, especially compared to VLOOKUP:

XLOOKUP can lookup data to the right or left of lookup values

XLOOKUP can return multiple results (example #3 above)
XLOOKUP defaults to an exact match (VLOOKUP defaults to approximate)
XLOOKUP can work with vertical and horizontal data
XLOOKUP can perform a reverse search (last to first)
XLOOKUP can return entire rows or columns, not just one value
XLOOKUP can work with arrays natively to apply complex criteria

Notes

1. XLOOKUP can work with both vertical and horizontal arrays.

2. XLOOKUP will return #N/A if the lookup value is not found.
3. The lookup_array must have a dimension compatible with the return_array
argument, otherwise XLOOKUP will return #VALUE!

https://exceljet.net/excel-functions/excel-xlookup-function 8/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

4. If XLOOKUP is used between workbooks, both workbooks must be open,

otherwise XLOOKUP will return #REF!.
5. Like the INDEX function, XLOOKUP returns a reference as a result.

XLOOKUP is a new function available in Excel 365 only.

XLOOKUP formula examples

Due date by category

In this example, the goal is to create a due date based
on category, where each category has a different
number of days allocated to complete a given task,
issue, project, etc. The amount of time available to
resolve ...

Reverse VLOOKUP example

Starting at the beginning, the formula in H5 is a
normal VLOOKUP formula: =VLOOKUP(G5,B5:D8,3,0)
// returns 3000 Using G5 as the lookup value ("C"), and
the data in B5:D8 as the table array, VLOOKUP
performs a...

XLOOKUP latest by date

XLOOKUP offers several features that make it
exceptionally good for more complicated lookups. In
this example, we want the latest price for an item by
date. If data were sorted by date in ascending order,
this would be...

SUMIFS vs other lookup formulas

If you are new to the SUMIFS function, you can find a
basic overview with many examples here. The SUMIFS
function is designed to sum numeric values based on
one or more criteria. In specific cases however, you
may be...
https://exceljet.net/excel-functions/excel-xlookup-function 9/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

Find closest match

At the core, this is an INDEX and MATCH formula:
MATCH locates the position of the closest match, feeds
the position to INDEX, and INDEX returns the value at
that position in the Trip column. The hard work is
done...

XLOOKUP lookup left

Whereas VLOOKUP is limited to lookups to the right of
the lookup column, XLOOKUP can lookup values to the
left natively. This means XLOOKUP can be used
instead of INDEX and MATCH to find values to the left
in a table...

XLOOKUP horizontal lookup

One nice benefit of the XLOOKUP function is that the
syntax used for horizontal lookups is the same as for
vertical lookups. In the example shown, the data in
C4:F5 contains quantity-based discounts. As the
quantity...

Name of nth largest value with criteria

The LARGE function is an easy way to get the nth
largest value in a range: =LARGE(range,1) // 1st largest
=LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd
largest In this example, we can use the LARGE...

XLOOKUP last match

By default, XLOOKUP will return the first match in a
data set. However, XLOOKUP offers an optional
argument called search_mode to control the order in
which data is searched. Setting search mode to -1
causes XLOOKUP to...

XLOOKUP two-way exact match

https://exceljet.net/excel-functions/excel-xlookup-function 10/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

One of XLOOKUP's features is the ability to lookup and

return an entire row or column. This feature can be
used to nest one XLOOKUP inside another to perform a
two-way lookup. The inner XLOOKUP returns a result
to the...

Detailed LET function example

In this example, we have a simple set of data in B5:D16
that includes ID, Name, and Points. The goal is to
generate a custom message for any name in the list by
entering a valid ID in cell G5. The message uses the
name...

XLOOKUP rearrange columns

This formula uses XLOOKUP twice, by nesting one
XLOOKUP inside another. The first (inner) XLOOKUP is
used to perform an exact match lookup on the value in
G5: XLOOKUP(G5,E5:E15,B5:E15) The lookup_value
comes from...

XLOOKUP basic exact match

In the example shown, cell G4 contains the lookup
value, "Berlin". XLOOKUP is configured to find this
value in the table, and return the population. The
formula in G5 is: =XLOOKUP(G4,B5:B18,D5:D18) // get
population...

XLOOKUP basic approximate match

In the example shown, the table in B4:C13 contains
quantity-based discounts. As the quantity increases,
the discount also increases. The table in E4:F10 shows
the discount returned by XLOOKUP for several
random...

XLOOKUP date of max value

This formula is based on the XLOOKUP function. Working from the inside out, we use
the MAX function to calculate a lookup value: MAX(values) MAX is nested inside
XLOOKUP, and returns a value directly as the first...
https://exceljet.net/excel-functions/excel-xlookup-function 11/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

Related videos

Basic XLOOKUP example

In this video, we’ll set up the XLOOKUP function with
a basic example. Matching on City name, we'll retrieve
Country and Population.

XLOOKUP with multiple lookup values

In this video, we'll set up XLOOKUP to return multiple
values in a dynamic array, by providing a range of
lookup values instead of a single lookup value.

Basic XLOOKUP approximate match

In this video, we’ll set up the XLOOKUP function to
perform an approximate match in order to calculate a
quantity based discount.

XLOOKUP with boolean logic

In this video we'll look how to use the XLOOKUP
function with Boolean logic to apply multiple criteria.

Related functions
Excel XMATCH Function
The Excel XMATCH function performs a lookup and returns a position in vertical or
horizontal ranges. It is a more robust and flexible successor to the MATCH function.

https://exceljet.net/excel-functions/excel-xlookup-function 12/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

XMATCH supports approximate and exact matching,

reverse search, and wildcards...

Excel FILTER Function

The Excel FILTER function filters a range of data based
on supplied criteria, and extracts matching records.

Excel VLOOKUP Function

VLOOKUP is an Excel function to look up data in a
table organized vertically. VLOOKUP supports
approximate and exact matching, and wildcards (* ?)
for partial matches. Lookup values must appear in the
first...

Excel INDEX Function

The Excel INDEX function returns the value at a given
location in a range or array. You can use INDEX to
retrieve individual values, or entire rows and columns.
The MATCH function is often used together with
INDEX to provide row and column...

Excel MATCH Function

MATCH is an Excel function used to locate the position
of a lookup value in a row, column, or table. MATCH
supports approximate and exact matching, and
wildcards (* ?) for partial matches. Often, MATCH is
combined...

Excel LOOKUP Function

The Excel LOOKUP function performs an approximate match lookup in a one-column
or one-row range, and returns the corresponding value from another one-column or

https://exceljet.net/excel-functions/excel-xlookup-function 13/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

one-row range. LOOKUP's default behavior makes it

useful for solving certain problems...

Excel HLOOKUP Function

The Excel HLOOKUP function finds and retrieve a value
from data in a horizontal table. The "H" in HLOOKUP
stands for "horizontal", and lookup values must appear
in the first row of the table, moving horizontally to the
right. HLOOKUP supports...

See also
Dynamic array formulas in Excel

500 Formulas | 101 Functions

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.

email address Get the PDF

https://exceljet.net/excel-functions/excel-xlookup-function 14/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

Topics

Formula Basics
Formula Examples
Conditional Formatting
Pivot Tables
Excel Tables
Excel Charts
Shortcuts
More...

Key functions

IF function
VLOOKUP function
XLOOKUP function
FILTER function
SUMIFS function
COUNTIFS function
SUMPRODUCT function
INDEX and MATCH
More functions...

https://exceljet.net/excel-functions/excel-xlookup-function 15/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa.

Our goal is to help you work faster in Excel. We
create short videos, and clear examples of formulas,
functions, pivot tables, conditional formatting, and
charts. Read more.

https://exceljet.net/excel-functions/excel-xlookup-function 16/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

https://exceljet.net/excel-functions/excel-xlookup-function 17/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

https://exceljet.net/excel-functions/excel-xlookup-function 18/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

https://exceljet.net/excel-functions/excel-xlookup-function 19/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

https://exceljet.net/excel-functions/excel-xlookup-function 20/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

https://exceljet.net/excel-functions/excel-xlookup-function 21/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

https://exceljet.net/excel-functions/excel-xlookup-function 22/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

https://exceljet.net/excel-functions/excel-xlookup-function 23/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

https://exceljet.net/excel-functions/excel-xlookup-function 24/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

https://exceljet.net/excel-functions/excel-xlookup-function 25/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

https://exceljet.net/excel-functions/excel-xlookup-function 26/27
29/12/2021, 11:03 How to use the Excel XLOOKUP function | Exceljet

Not far from 40 years (beg. with Visicalc in 82) of intensive

spreadsheet use (+develop solutions +teach) and fully
amazed with so many great ideas of formulas found in your
examples. -jhbf

Excel video training

Quick, clean, and to the point.
Learn more

Home About Blog Contact

© 2012-2021 Exceljet. Terms of use
Feedback

https://exceljet.net/excel-functions/excel-xlookup-function 27/27