Practice free

XLOOKUP function

XLOOKUP searches one range for a value and returns the matching item from another range, in any direction. It replaces most VLOOKUP and INDEX/MATCH formulas, and exact match is the default.

Example

Read it as a sentence: look for Cigar in the Item column and give me its Unit Value.

Practice this in the app

How XLOOKUP works

XLOOKUP answers one kind of question: I know one thing about a row, so what is another thing in that same row? You know a customer's name and want their order total. You know a product code and want its price.

You tell it three things: what to look for, the column to look in, and the column to bring the answer back from. Excel goes down the first column until it finds your value, then hands back whatever sits in the same position in the second column.

In the example above, Cigar is in row 5 of the Item column, so Excel returns the value in row 5 of Unit Value. The sheet shows $10.00 because that column is formatted as dollars. The formula's cell has no format yet, so it shows the plain number, 10.

The other three arguments are optional. They decide what to show when nothing is found, whether a near match is good enough, and which end of the list to start from.

Syntax

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

Arguments in [square brackets] are optional.

What to look for lookup_value (required)
The value you are searching for.
Where to look lookup_array (required)
The row or column to search.
What to bring back return_array (required)
The row or column to return from.
What to show if it's missing if_not_found (optional)
What to show if there is no match; default is #N/A.
How close the match must be match_mode (optional)

Leave it out and only an exact match counts.

  • 0Exact match only. This is what you get when you leave it out.
  • -1Exact match, or else the next smaller value.
  • 1Exact match, or else the next larger value.
  • 2Wildcards: * stands for any run of characters.
  • 3A regex pattern. ^Cl means text that starts with Cl.
Which end to start from search_mode (optional)

It matters when the value appears more than once, because XLOOKUP stops at the first match it meets.

  • 1Start at the top. This is what you get when you leave it out.
  • -1Start at the bottom, so you get the last match.
  • 2Binary search on a list sorted smallest to largest. Item is sorted A to Z, so this works.
  • -2Binary search on a list sorted largest to smallest. Item is sorted the other way, so this misses.

Returns: The matching item(s) from return_array; can spill several cells.

XLOOKUP returns one match, not every match: the first one, or the last if you search from the bottom. If the return range has several columns, you get that row's value from each one, and they spill into the cells to the right of the formula.

More examples

Each picture shows only the columns that formula reads.

  1. Show your own text when nothing matches

    What should the cell say when the item is not in the list?

    There is no Deerstalker in the Item column. The fourth argument is what Excel shows in that case. Leave it out and the same lookup ends in #N/A.

    • =XLOOKUP("Deerstalker",Items!B2:B7,Items!E2:E7) #N/A
  2. Look to the left, and find the last match

    Which is the last item listed in the Smoking category?

    The answer column can sit on either side of the search column. Here Category is searched and Item, to its left, comes back. The sixth argument, -1, starts from the bottom, so you get the last Smoking row, Clay pipe. Without it you get the first one, Briar pipe.

    • =XLOOKUP("Smoking",Items!C2:C7,Items!B2:B7) Briar pipe
  3. Find the nearest value when there is no exact match

    No place is exactly 5 miles from Baker Street. Which is the farthest one inside 5 miles?

    A fifth argument of -1 settles for the next smaller value, which is 3.4, so the answer is Waterloo Station. Change it to 1 and Excel takes the next larger value, 9.5, and answers Pondicherry Lodge. The Miles column is not sorted, and it does not need to be.

    • =XLOOKUP(5,Places!E2:E16,Places!B2:B16,,1) Pondicherry Lodge
  4. Match part of a name with a wildcard

    What is the value of the item whose name starts with Com?

    Com* means anything that starts with Com, and it finds Commonplace book and its value, 6. It only works when the fifth argument is 2. Leave that out and Excel searches for the literal text Com* and finds nothing.

    • =XLOOKUP("Com*",Items!B2:B7,Items!E2:E7) #N/A
  5. Bring back the whole row

    What do we know about Mrs. Hudson?

    Give it three answer columns and one formula returns all three. The formula's own cell shows the first value, Landlady, and her Home and Age spill into the cells to its right.

  6. Search across a row instead of down a column

    Which column sits under the heading Miles?

    The search range is the heading row, and the answer range is the whole table. Excel finds Miles in the fifth heading and returns that column. The formula's cell shows its first value, 0, and the rest spills down.

  7. Look up several values with one formula

    What are the cigar and the candle each worth?

    Give it a list of items and it returns one answer per item, spilling down. The formula's cell shows the cigar's 10, and the candle's 2 lands in the cell below.

  8. Join two tables

    Who owns the cigar?

    The Items sheet only holds the owner's ID, P01. The inner XLOOKUP finds that ID, and the outer one looks it up on the People sheet and returns the name.

Watch out for

It only accepts an exact match unless you say otherwise

Leave match_mode out and XLOOKUP wants the lookup value exactly as it appears in the list. Anything else ends in #N/A. Upper and lower case do not matter, so cigar still finds Cigar.

  • =XLOOKUP("Deerstalker",Items!B2:B7,Items!E2:E7) #N/A
  • =XLOOKUP("cigar",Items!B2:B7,Items!E2:E7) 10

Both ranges must be the same size

lookup_array and return_array have to cover the same number of rows. Here the items run from row 2 to row 7 but the return range stops at row 6, and Excel answers #VALUE! even though Cigar sits inside both.

  • =XLOOKUP("Cigar",Items!B2:B7,Items!E2:E6) #VALUE!

Text that looks like a number is not a number

The Qty column holds the number 12. Searching for "12" in quotes looks for text, so nothing matches. Without the quotes it finds Boxer cartridge. This is the usual cause when a lookup fails on data pasted or imported from another system.

  • =XLOOKUP("12",Items!D2:D7,Items!B2:B7) #N/A
  • =XLOOKUP(12,Items!D2:D7,Items!B2:B7) Boxer cartridge

A date comes back as a plain number

XLOOKUP returns the value, not the formatting. The cigar's Acquired date is 2/11/2025, and the formula's cell shows 45699, the number Excel stores for that day. Format the cell as a date to read it.

  • =XLOOKUP("Cigar",Items!B2:B7,Items!H2:H7) 45699

Several return columns need empty cells beside the formula

When return_array is more than one column wide, the answer spills across that many cells. Keep the cells to the right of the formula empty so the result has room.

Excel 2019 and earlier do not have it

XLOOKUP arrived in Excel 2021. If people on older versions open your workbook, build the lookup with INDEX and MATCH or with VLOOKUP instead.

Match and search options

match_mode: how close the match must be

Leave it out and only an exact match counts.

ValueWhat it doesMeasured exampleExcel shows
0 Exact match only. This is what you get when you leave it out. =XLOOKUP("Cigar",Items!B2:B7,Items!E2:E7) 10
-1 Exact match, or else the next smaller value. =XLOOKUP(5,Places!E2:E16,Places!B2:B16,,-1) Waterloo Station
1 Exact match, or else the next larger value. =XLOOKUP(5,Places!E2:E16,Places!B2:B16,,1) Pondicherry Lodge
2 Wildcards: * stands for any run of characters. =XLOOKUP("Com*",Items!B2:B7,Items!E2:E7,,2) 6
3 A regex pattern. ^Cl means text that starts with Cl. =XLOOKUP("^Cl",Items!B2:B7,Items!B2:B7,,3) Clay pipe
search_mode: which end to start from

It matters when the value appears more than once, because XLOOKUP stops at the first match it meets.

ValueWhat it doesMeasured exampleExcel shows
1 Start at the top. This is what you get when you leave it out. =XLOOKUP("Smoking",Items!C2:C7,Items!B2:B7) Briar pipe
-1 Start at the bottom, so you get the last match. =XLOOKUP("Smoking",Items!C2:C7,Items!B2:B7,,0,-1) Clay pipe
2 Binary search on a list sorted smallest to largest. Item is sorted A to Z, so this works. =XLOOKUP("Cigar",Items!B2:B7,Items!E2:E7,,0,2) 10
-2 Binary search on a list sorted largest to smallest. Item is sorted the other way, so this misses. =XLOOKUP("Cigar",Items!B2:B7,Items!E2:E7,,0,-2) #N/A

Questions people ask

Is XLOOKUP case-sensitive?

No. Typing cigar in lower case still finds Cigar.

  • =XLOOKUP("cigar",Items!B2:B7,Items!E2:E7) 10
What does XLOOKUP show when the cell it finds is empty?

It shows 0, not a blank. James Moriarty is in the Name column, but his Home cell is empty, and Excel shows 0.

  • =XLOOKUP("James Moriarty",People!B2:B16,People!D2:D16) 0
Why does my XLOOKUP return #N/A?

It found no match. The usual reasons are a value that is not in the list, text being compared with a number, or a wildcard used without match_mode 2. The Watch out for section shows each one.

Where it works

Excel
XLOOKUP arrived in Excel 2021. Older versions do not have it.
Google Sheets
Same name, same behaviour for everyday use. Sheets has XLOOKUP with the same arguments.

Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .