Excel’s XLOOKUP function is one of its greatest additions in recent years, offering users a fast, robust, and flexible lookup experience without the limitations of older functions like VLOOKUP.
How XLOOKUP Works
XLOOKUP lets you find data in a table by matching it with one or more columns. The syntax is:
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
Where:
lookup_value– The value searched for in the first column of the lookup arraylookup_array– The table you want to search, with lookup values in the first columnreturn_array– The column with data to returnif_not_found(optional) – Value to return if no match is found
So XLOOKUP scans the first column in your lookup array to find the lookup value. When it gets a match, it returns the corresponding value from the return column.
It’s extremely flexible – you can lookup to the left, right, up or down without complex nested functions.
Key Advantages of Using XLOOKUP
XLOOKUP brings some meaningful improvements:
Performance Improvements
Tests confirm that XLOOKUP is much faster than alternatives like VLOOKUP, especially on large data sets. This boost can make a real difference in heavy spreadsheets.
It achieves this through optimized algorithms and by directly accessing values in memory instead of reading from disk.
Flexibility
As shown above, XLOOKUP lets you search in any direction without fuss. VLOOKUP forces you to organize your data with lookup values on the left, but XLOOKUP removes that limitation.
You can also return multiple matches without array functions. This avoids complex array formulas that are prone to error.
Error Handling
Errors happen, but XLOOKUP lets you control what happens when no match is found. The if_not_found parameter replaces #N/A errors with something readable.
Spill Ranges
When returning multiple rows or columns, XLOOKUP outputs its results into a “spill range” automatically extending down or across cells.
This eliminates the need for Ctrl+Shift+Enter array formulas that overflow without warning. Spill ranges make it obvious when more space is needed.
Use Cases and Examples
XLOOKUP is built for versatility. Here are some examples:
Basic Vertical Lookup
Just like VLOOKUP, you can search down a table:
=XLOOKUP(A2,C:C,D:D,"Not found")
This looks up the value in cell A2 within the lookup column C:C, returning the corresponding row from column D:D. If no match appears, it returns “Not found”.
Horizontal Lookup
To search left instead of down:
=XLOOKUP(F2,1:1,1:1)
Now it searches horizontally along row 1 for the lookup value in F2, returning the match from row 1.
Lookup with Error Handling
Handle errors by adding a custom message:
=XLOOKUP(A2,E:E,F:F,"No data for "&A2)
So if A2 isn’t found in column E, it will return “No data for [value in A2]”.
Returning Multiple Matches
To output all matching rows or columns:
=XLOOKUP(N2,D:D,E:E)
This will spill horizontally across columns if N2 is found multiple times in column D. No Ctrl+Shift+Enter needed!
These are just a few possibilities – XLOOKUP is designed for creative solutions.
Comparison to Other Lookup Functions
How does XLOOKUP stack up against previous options?
VLOOKUP
VLOOKUP is extremely widespread, but has drawbacks like strictly vertical lookups and required sorted lookup columns. Performance is also slower than XLOOKUP in many cases.
Still, it takes time to phase out old functions. VLOOKUP has the advantage of very broad compatibility. XLOOKUP only works on Office 365 subscriptions.
HLOOKUP
HLOOKUP allows left-to-right lookups, but shares other VLOOKUP disadvantages around sorting requirements and speed.
XLOOKUP makes HLOOKUP redundant by offering flexible lookup directions.
INDEX/MATCH
Expert Excel users often use INDEX/MATCH instead of VLOOKUP to avoid limitations. But it uses more resources and requires understanding of advanced concepts like array formulas.
For advanced cases, INDEX/MATCH still has advantages. But XLOOKUP makes simple and common lookups easier without sacrificing flexibility.
Tips for Effective Use
Keep these tips in mind when working with XLOOKUP:
Watch Out For Blank Cells
Blanks cells count as lookup values, so be careful of false partial matches. Filter blanks or use IFERROR to handle them.
Use Absolute and Relative References
Use absolute ($) references to lock columns, and relative references to lookup values that change. This avoids updating formulas manually.
Combine With Other Functions
Use IFERROR, IFS or other logical functions to handle multiple conditions. For example:
=IFERROR(XLOOKUP(A2,B:B,C:C),"Not in range")
Conclusion
XLOOKUP brings a fast, flexible lookup experience free from many limitations users previously faced. It deserves consideration from any Excel user still relying on older functions like VLOOKUP.
Adoption is still ongoing, but its optimizations and straightforward syntax mean XLOOKUP is likely here to stay. For analysts, accountants, financial professionals, and power users who live in spreadsheets, time invested in XLOOKUP mastery will pay dividends.
While complex cases still benefit from INDEX/MATCH, XLOOKUP more than handles simple and common usage. It removes headaches around sorted columns, vertical-only lookups, and complex array formulas. Performance, error handling, and spill ranges provide additional benefits.
In short, XLOOKUP is a milestone for Excel users everywhere. Lookup, now faster and more flexible than ever before.
FAQ
What are the main advantages of XLOOKUP over VLOOKUP?
XLOOKUP brings a major performance boost through its algorithms optimized for speed. Unlike VLOOKUP, it can also lookup in any direction, handle errors gracefully, and output spill ranges to show multiple results by default.
How do I perform a two-way lookup with XLOOKUP?
Use XLOOKUP within an INDEX function to search based on two criteria instead of one, similar to VLOOKUP with approximate match. This lets you lookup using values in both the first column and first row.
Can I use XLOOKUP on Excel versions earlier than Office 365?
Unfortunately XLOOKUP is only available on Microsoft 365 subscriptions. Older Excel versions don’t have access yet.
Conclusion
XLOOKUP brings meaningful advantages free from common limitations, and is fast becoming an essential tool for any Excel user.