MEET
XLOOKUP
Step up to Modern Excel, work smarter, not harder.
Discover how you can leave VLOOKUP behind
…and switch to a smarter function that replaces
both VLOOKUP and HLOOKUP, with fewer errors
and more flexibility!
Lukáš Kucharczyk Get my newsletter and receive
@SimplyDigital more tips like this.
BASIC SYNTAX
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],
[match_mode], [search_mode])
lookup_array return_array lookup_value
🗂️Where we’re ✅ What we want to The "what" we’re
looking get back looking for 🔍
NOTES:
Lookup and return arrays must be the same size, or you'll get a #VALUE! error.
XLOOKUP returns the item corresponding to the first match it finds.
Lookup_value can be an array – returns multiple results and spills automatically
HANDLING ERRORS
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],
[match_mode], [search_mode])
In the example, "Czechia" is not present in the country column. When using XLOOKUP,
we added the text "Country not found" in quotation marks as the if_not_found parameter.
As a result, XLOOKUP returns this custom message instead of the default #N/A error.
NOTES:
The if_not_found parameter allows you to handle missing values directly in a single
formula.
By default, when XLOOKUP can't find the value you're searching for, it returns a #N/A
error.The #N/A error means "Not Available", in other words, the searched value
doesn't exist in the lookup array.
This is where the if_not_found argument comes in. It's optional, but in many cases, it's
worth using. You can insert your own custom text or leave the result blank – whichever
fits your situation best.
XLOOKUP WITH
VISUAL FEEDBACK
Using XLOOKUP with the if_not_found argument is a great way to handle missing data.
But in large Excel tables with hundreds of rows, it can be even more helpful to make those
missing values stand out visually.That’s where conditional formatting comes in.
Instead of just showing a custom message like "Not found", you can highlight those cells
in red to grab attention instantly. This combination of XLOOKUP + conditional formatting is
commonly used in real-world spreadsheets.
It's especially useful when working with status values like OK / NOK, where green and red
formatting clearly shows what’s good and what needs attention.
The easiest way to apply conditional formatting:
Select the desired range and go to: Home →
Conditional Formatting → Highlight Cells
Rules → Equal To
DYNAMIC ARRAYS
XLOOKUP can return multiple values at once using spill.
With one formula, you can fill several rows automatically, no copying needed.
In this example, we use a 4-cell input range (F22:F25).
Type any fruit name, and XLOOKUP instantly returns the price per ton from the source
table.
Source Table User Input XLOOKUP Formula
Where we look up the can be changed Returns results in 4
information. anytime. rows automatically.
The final formula looks like this:
=XLOOKUP(F22:F25, FruitPrice[Product Name],
FruitPrice[Price per ton], "Not found")
MATCH MODE
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],
[match_mode], [search_mode])
The match_mode parameter in XLOOKUP controls how Excel searches for your lookup
value, letting you choose between exact matches, closest matches, or wildcard searches.
It defines how flexible or strict the match should be when finding results.
The match_mode is an optional argument in XLOOKUP. This means you do not have to fill
it in. If you leave it empty, Excel uses the default value 0.
Options:
0 - Exact match
Looks for the exact value you type. If nothing is found, it returns #N/A.
Use this when you need an exact match, for example finding a specific product code or ID.
-1 – Exact match or next smaller item
Looks for the exact value. If it is not found, it returns the next smaller value.
Use this when working with ranges or numbers, for example finding a tax rate for an income
that falls between brackets.
1 – Exact match or next larger item
Looks for the exact value. If it is not found, it returns the next larger value.
Use this when you want the closest higher value, for example finding the next available
delivery date.
2 – Wildcard match
Allows using * for any number of characters, ? for one character, and ~ to search for a real *
or ?.
Use this when searching for text patterns, for example finding all names starting with "A" or
codes ending with "X".
MATCH MODE
Example: Using Match_Mode to Find Discounts
One way to use the match_mode argument in XLOOKUP is to calculate a discount based
on the order size. In this example, we will search in a discount table.
Depending on the total purchase value, XLOOKUP will return the discount that the
customer receives.
Table for Searching
Customer Total Order
Discounts
XLOOKUP Formula to
Find Discount Based
on Total Order Value
