The XLOOKUP function is Microsoft Excel’s latest and most advanced lookup formula, designed to replace older functions like VLOOKUP, HLOOKUP, and LOOKUP. It allows users to search for and return matching values across rows or columns, making it an essential tool for efficient data lookup and analysis in Excel spreadsheets.
Key Takeways:
- What is the XLOOKUP function and why should you use it
- How does the XLOOKUP function work?
- What is the difference between the Excel Lookup Functions – Xlookup, Vlookup and Hlookup
If you’re still building your foundation, our beginner-friendly WSQ Workplace Productivity Using Excel l course at James Cook Institute is a great place to start
Why LOOKUP functions matter?
Excel offers various built-in tools for searching through data, such as conditional formatting and the Find & Select feature. While helpful, these tools don’t allow you to retrieve or reference values dynamically in formulas, especially not when building automated reports or dashboards.
That’s where Excel Lookup functions come in. If you’re working with spreadsheets where the data changes regularly, you’ll often need to retrieve specific results, such as a name, price, or ID. This is a common scenario for professionals working with data, including sales teams, HR staff, and data analysts.
What is XLOOKUP and why should you use it?
The XLOOKUP function is designed to search through a range or array and return a value that corresponds to the first match it finds. Unlike its predecessors — VLOOKUP, HLOOKUP, and LOOKUP — XLOOKUP is far more versatile and modern in its capabilities.
What sets it apart is its ability to:
- Search both vertically and horizontally, in any direction — left, right, up, or down
- Use multiple criteria for more complex lookups
- Perform approximate matches when needed, while defaulting to exact match for better accuracy
- Handle partial matches using wildcard characters
- Return entire rows or columns, not just a single value
- Display a custom message if no match is found, avoiding unhelpful #N/A errors
On top of that, XLOOKUP is optimised for performance — it runs faster than older lookup functions, which can make a noticeable difference when working with large datasets or dynamic reports.
In short, XLOOKUP brings together the best of previous lookup functions while removing their limitations, making it the go-to choice for Excel users.
XLOOKUP Availability
As of now, the XLOOKUP function is available in Excel 365, Excel 2021, and Excel for the web. It’s important to note that XLOOKUP is not backward compatible, meaning it won’t function in earlier versions.
How does XLOOKUP function work?
To see how XLOOKUP works in its simplest form, let’s walk through an exact match scenario using just its first three arguments.
Suppose you have a dataset listing countries along with their size and area. You want to retrieve the area of a country specified in cell F7. With country names listed in A6:A12 (the lookup array) and their corresponding areas in C6:C12 (the return array), the formula looks like this:
=XLOOKUP(F7, A6:A12, C6:C12) – this tells Excel: “Look for the country name entered in F7 within the list A6:A12 and return the corresponding value from C6:C12 in the same row.
XLOOKUP Vertically and Horizontally
Microsoft Excel used to have two functions for different lookup types, each having its own syntax and usage rules: VLOOKUP to look vertically in a column and HLOOKUP to look horizontally in a row.
The XLOOKUP function can do both with the same syntax. The difference is in what you provide for the lookup and return arrays.
Approximate match XLOOKUP
In cases where you need to assign results based on score ranges (such as grades) XLOOKUP can be used for approximate matching by setting the match_mode argument to 1.
This tells Excel to look for an exact match first, and if it doesn’t find one, return the next smaller value – assuming your lookup array is sorted in ascending order.
In this example, the table in C4:D8 defines the minimum score required for each grade:
0 = E
60 = D
70 = C
80 = B
90 = A
The formula: = XLOOKUP(G4, $C$4:$C$8, $D$4:$D$8, “Not Found”, 1)
Why XLOOKUP is Better Than VLOOKUP
| Feature | Hlookup | Vlookup | Xlookup |
| Purpose | Horizontal lookup (searches in top row) | Vertical lookup (searches in leftmost column) | Performs both horizontal and vertical lookups |
| Lookup Direction | Only searches below the top row | Only searches to the right of the lookup column | Can search in any direction: Left, right, up, down |
| Match Mode | Defaults to approximate match. Requires FALSE for exact match | Defaults to approximate match. Requires FALSE for exact match | Defaults to exact match. Can be set to approximate |
| Return Value Location | Returns values from a specified row number | Returns values from a specified column index | Returns from a specified array (no index needed) |
| Supports Horizontal Lookup | Yes (primary use case) | No | Yes |
| Supports Vertical Lookup | No | Yes (primary use case) | Yes |
| Column/Row Insertion Safe? | No. breaks if row numbers shift | No. Breaks if columns are inserted or deleted | Yes. Uses direct references, not index numbers |
| Custom Error Handling | No. Requires IFERROR() or IFNA() | No. Requires IFERROR() or IFNA() | Yes. Built-in `if_not_found` parameter |
| Partial Match with Wildcards | Yes. Supported with exact match and wildcards | Yes. Supported | Yes. Supported and more flexible |
| Search Mode Control | Not supported | Not supported | Yes. Can search forward or reverse (`search_mode`) |
| Return Multiple Values | No. Can return only one value | No. Can return only one value | Yes, can return multiple columns or rows |
| Array Handling | Entire table must be referenced | Entire table must be referenced | Lookup and return arrays can be separate |
| Performance on Large Data | Slower on large, unsorted ranges | Slower on la rge unsorted ranges | More efficient and direct |
| Compatibility | Supported in all Excel versions | Supported in all Excel versions | Available only in Excel 2021 and Microsoft 365 |
| Use Case Today | Rarely used unless data is structured horizontally | Common for legacy files and basic lookups | Best choice for modern, scalable, and flexible lookup tasks |
Why XLOOKUP is Better Than VLOOKUP
XLOOKUP outperforms both VLOOKUP and HLOOKUP, particularly when working with large datasets or more advanced lookup needs. Its ability to search in any direction, return multiple results, and remain stable even when rows or columns are added makes it a highly versatile and modern function.
That said, VLOOKUP and HLOOKUP still have their place.
- VLOOKUP remains useful for basic vertical lookups, especially in older versions of Excel.
- HLOOKUP, though more limited, is occasionally used when working with data laid out horizontally, though in most cases, XLOOKUP now covers that functionality more effectively.
Ultimately, the best lookup function depends on your Excel version and the complexity of your task. If you’re using Excel 2021 or Microsoft 365, learning XLOOKUP is well worth the time
If you’re new to Excel or want to build a strong foundation, our Workplace Productivity using Excel is an excellent starting points to master essential functions, For those ready to take their skills further, we also offer intermediate to advanced Excel with our WSQ Level Up Excel Course that covers Lookup functions.
