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.
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.
-
Show your own text when nothing matches
What should the cell say when the item is not in the list?
Excel shows Not in the roomsBE1ItemUnit Value2Boxer cartridge$3.503Briar pipe$4.254Candle$2.005Cigar$10.006Clay pipe$1.757Commonplace book$6.00ItemsThere 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
-
-
Look to the left, and find the last match
Which is the last item listed in the Smoking category?
Excel shows Clay pipeBC1ItemCategory2Boxer cartridgeTools3Briar pipeSmoking4CandleHousehold5CigarSmoking6Clay pipeSmoking7Commonplace bookPapersItemsThe 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
-
-
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?
Excel shows Waterloo StationBE1PlaceMiles2221B Baker Street03Scotland Yard2.84Diogenes Club1.95Barts3.16Paddington1.27Waterloo Station3.48Simpson's2.39Lyceum Theatre2.210Pondicherry Lodge9.511Briony Lodge1.612Saxe-Coburg Square2.913Reichenbach Fall62014Baskerville Hall21515Stoke Moran2416Copper Beeches68PlacesA 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
-
-
Match part of a name with a wildcard
What is the value of the item whose name starts with Com?
Excel shows 6BE1ItemUnit Value2Boxer cartridge$3.503Briar pipe$4.254Candle$2.005Cigar$10.006Clay pipe$1.757Commonplace book$6.00ItemsCom* 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
-
-
Bring back the whole row
What do we know about Mrs. Hudson?
Excel shows LandladyBCDE1NameRoleHomeAge2Sherlock HolmesDetectiveL01413John H. WatsonDoctorL01434Mrs. HudsonLandladyL01585Mycroft HolmesCivil servantL03486G. LestradeInspectorL02467Tobias GregsonInspectorL02448Stanley HopkinsInspectorL02309Irene AdlerSingerL103010James MoriartyProfessor5511Sir Henry BaskervilleClientL133112Helen StonerClientL143213Violet HunterClientL152714Jabez WilsonClientL116015WigginsIrregular1416BillyPageL0115PeopleGive 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.
-
Search across a row instead of down a column
Which column sits under the heading Miles?
Excel shows 0ABCDEFG1Place IDPlaceTypeRegionMilesCab FareVisits2L01221B Baker StreetResidenceLondon0$0.003653L02Scotland YardPoliceLondon2.8$1.75414L03Diogenes ClubClubLondon1.9$1.2595L04BartsHospitalLondon3.1$2.00126L05PaddingtonStationLondon1.2$0.75177L06Waterloo StationStationLondon3.4$2.25148L07Simpson'sRestaurantLondon2.3$1.50229L08Lyceum TheatreTheatreLondon2.2$1.50310L09Pondicherry LodgeResidenceLondon9.5$5.50211L10Briony LodgeResidenceLondon1.6$1.00412L11Saxe-Coburg SquareShopLondon2.9$1.75313L12Reichenbach FallLandmarkSwitzerland620$48.00114L13Baskerville HallCountry houseDevonshire215$18.50215L14Stoke MoranCountry houseSurrey24$3.25116L15Copper BeechesCountry houseHampshire68$6.751PlacesThe 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.
-
Look up several values with one formula
What are the cigar and the candle each worth?
Excel shows 10BE1ItemUnit Value2Boxer cartridge$3.503Briar pipe$4.254Candle$2.005Cigar$10.006Clay pipe$1.757Commonplace book$6.00ItemsGive 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.
-
Join two tables
Who owns the cigar?
Excel shows Sherlock HolmesBF1ItemOwner2Boxer cartridgeP013Briar pipeP014CandleP035CigarP016Clay pipeP017Commonplace bookP01ItemsThe 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.
| Value | What it does | Measured example | Excel 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.
| Value | What it does | Measured example | Excel 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 .