Study smart | Excel superstars
Things are looking up
Frustrated when small changes to your spreadsheet break your VLOOKUP searches? You need to try XLOOKUP
Words Traci Williams, excelace.co.uk
If you’ve used Excel for any length of time, you’ve almost certainly used VLOOKUP. And if you’ve used VLOOKUP long enough, you’ve almost certainly been bitten by it, too.
- Wrong column numbers
- Broken formulas after inserting columns as column numbers don’t update automatically
- Only being able to return a result from a column AFTER the match
- Approximate matches returning completely incorrect answers.
For years, advanced users moved to INDEX/MATCH as the more powerful and reliable solution.
Then in 2019, Microsoft introduced XLOOKUP, a function designed to replace both VLOOKUP and INDEX/MATCH.
What XLOOKUP does
The very basic intention of the XLOOKUP is:
“Find this value… and return the corresponding result from somewhere else.”
Basic Syntax
=XLOOKUP(lookup_value, lookup_array, return_array)
Example: =XLOOKUP(A2,Data!$A$2:$A$16,Data!$D$2:$D$16)
Translation:
Find the Order ID from A2 inside the Order ID column, and return the matching Customer Name.
That’s it, No column numbers, No fragile references, No need for workarounds.
Why XLOOKUP replaced VLOOKUP
Let’s compare them.
VLOOKUP =VLOOKUP(A2, A:D, 4, FALSE)
XLOOKUP =XLOOKUP(A2, A:A, D:D)
Immediately we see three major improvements:
Exact match by default
In VLOOKUP, this formula:
=VLOOKUP(A2,A:D) returns an approximate match, as it does not include TRUE/FALSE (or 1/0) at the end.
Which means Excel might return the wrong record….and you wouldn’t know.
XLOOKUP does the opposite:
=XLOOKUP(A2,A:A,D:D)
If the value doesn’t exist → it returns #N/A
This is safer, more transparent and trustworthy.
Built-in IFERROR
Instead of wrapping formulas like this:
=IFERROR(VLOOKUP(A2,A:D,4,FALSE),"Order Not Found")
XLOOKUP has it built in:
=XLOOKUP(A2,A:A,D:D,"Order Not Found")
So we can include within the XLOOKUP, what to return if the item is not found.
Cleaner formulas = easier maintenance
Searching left
VLOOKUP only works left to right, so with this data we couldn’t use vlookup to return the Customer ID from searching the Customer Name:
XLOOKUP will work anywhere.
=XLOOKUP(B2, Data!D:D, Data!C:C)
You can now return values from before the lookup column — no INDEX/MATCH needed.
Comparing to INDEX/MATCH
Before XLOOKUP, advanced users preferred:
=INDEX(Data!D:D, MATCH(B2, Data!C:C, 0))
It solved VLOOKUP’s problems:
- Didn’t break when columns inserted
- Allowed left lookup
- Exact match
But it introduced a new problem – complexity. Two functions (Index & Match):
- Harder to read
- Harder to teach
- Harder to debug
XLOOKUP replaces both with one readable formula:
=XLOOKUP(B2, Data!D:D, Data!C:C)
Formula length
Typical lookup formulas:
Shorter formulae significantly reduce debugging time and support costs, especially in large corporate workbooks.
People are also not as afraid of shorter formulae.
Advanced features in XLOOKUP
1) Search direction
Find last occurrence:
=XLOOKUP(A2, A:A, D:D,,,-1)
This is impossible in VLOOKUP without helper columns.
2) Approximate matches
Closest smaller value: =XLOOKUP(A2,A:A,D:D,,-1)
Closest larger value: =XLOOKUP(A2,A:A,D:D,,1)
Unlike VLOOKUP, these are explicit not accidental.
There are also a further two match types exclusive to XLOOKUP:
Wildcard: =XLOOKUP(A2,A:A,D:D,,2)
Finds partial text matches using * (any characters) and ? (single character) – useful when you don’t know the exact value.
Regex: =XLOOKUP(A2,A:A,D:D,,3)
Matches values based on a defined pattern or format (such as codes or structured text) rather than exact wording.
3) Return multiple columns
=XLOOKUP(A2, A:A, D:F)
One formula returns multiple results.
Previously required:
- Multiple VLOOKUPs
- Or complex INDEX/MATCH arrays
Performance
XLOOKUP performs faster than equivalent INDEX/MATCH in large datasets due to optimized calculation engine usage. This is especially noticeable above ~50,000 rows.
Summary comparison
When should you still use VLOOKUP?
Almost never. Only when:
- Using Excel 2016 or earlier
- Supporting legacy corporate templates
Otherwise, XLOOKUP should be the new standard.
Real-world examples
Most Excel functions sound simple in theory — but their value only becomes clear in real scenarios.
Example 1 — Price lists that change regularly
Imagine you maintain a product list used by multiple departments:
A traditional VLOOKUP might look like: =VLOOKUP(A2,PriceList!A:B,2,FALSE)
This works perfectly…..until someone inserts a column into the price list:
Suddenly column 2 is now the Cost Price, not the Price…..and the spreadsheet silently returns the wrong information.
With XLOOKUP: =XLOOKUP(A2,PriceList!A:A,PriceList!B:B)
Even if columns move, the formula will automatically update and still point at the correct return column.
This is one of the biggest real-world advantages: XLOOKUP removes maintenance risk, not just formula difficulty.
Example 2 — Employee records
Suppose an employee appears multiple times in a log i.e. Excel Training attendance:
We want to find the latest training date.
Previously this required complex formulas or helper columns, as a VLOOKUO would always return the FIRST entry it found.
With XLOOKUP: =XLOOKUP(("Sam",A:A,B:B,,,-1)
The -1 search mode tells Excel to search from bottom to top — returning the last occurrence.
A task that once required advanced Excel knowledge now becomes beginner-friendly and super simple.
Example 3 — Returning multiple fields
If we wanted to return three pieces of information, such as:
- Customer Name
- Date
- Salesperson
With VLOOKUP, we would need 3 separate formulas, one per column.
With XLOOKUP, we need just one:
=XLOOKUP(A2,Data!A:A,CHOOSE({1,2,3},Data!D:D,Data!B:B,Data!I:I),"Order Not Found")
Excel spills all results into the respective columns automatically:
This dramatically reduces formula count — which improves both performance and readability.
Common mistakes XLOOKUP prevents
Many spreadsheet errors aren’t caused by incorrect formulae; they’re caused by formulae that still calculate but return the wrong answer. This is even worse than an incorrect formula.
XLOOKUP eliminates several of the most common causes:
- Incorrect column number in VLOOKUP
- Approximate match used accidentally
- Columns inserted breaking reports
- Copy-paste formulas referencing wrong columns
- Hidden errors masked by IFERROR
In other words: XLOOKUP doesn’t just make Excel easier — it makes Excel safer.
Conclusion
The biggest impact of XLOOKUP isn’t speed or simplicity, it’s confidence.
When a workbook relies on VLOOKUP, you must constantly question whether the results are still correct after edits. With XLOOKUP, you can trust that structural changes won’t silently corrupt your answers. That alone can save hours of checking and reconciliation.
As well as improving lookups, XLOOKUP removed an entire category of spreadsheet mistakes.
For 30+ years, Excel users learned workarounds. Now the workaround is the function.
VLOOKUP taught a generation how Excel worked.
INDEX/MATCH taught a generation how to fix Excel.
XLOOKUP lets you just get your job done.
QUIZ TIME
Test your knowledge
Roll over the buttons to find the green correct answer or click on the buttons to find the correct answer
1
What is the biggest safety improvement of XLOOKUP?
2
Why does VLOOKUP commonly return wrong answers?
3
Which formula replaces INDEX/MATCH?
The Association of Accounting Technicians. 30 Churchill Place, London E14 5RE. Registered charity no.1050724. A company limited by guarantee (No. 1518983).