Excel | XLOOKUP – Syntax, Guide, Examples of Lookup Values, Differences vs VLOOKUP

I haven’t written anything about Excel for a long time, and today I’ve chosen the XLOOKUP function, which I shamefully haven’t described here yet. The function was officially introduced in September 2019, initially in Office 365 versions, and gradually became a standard part of Excel, including older versions. The main reason for introducing XLOOKUP is to replace the outdated VLOOKUP and HLOOKUP functions.

Like its predecessors, XLOOKUP is used to search data in tables, but the new function is far superior – both in terms of syntax simplicity and usability, as well as performance. Among its most significant advantages is that we no longer need to sort data before using the function to make it work correctly – the new function works bidirectionally (it can search left-to-right or right-to-left), has fewer required parameters, etc. (more in the section Summary of XLOOKUP Advantages). Let’s go step by step..

XLOOKUP Function Syntax and Parameters

The basic syntax of the XLOOKUP function is below. Values in square brackets are optional – this means the function is simpler, and only 3 arguments are required:  ¹

=XLOOKUP(lookup_value; lookup_array_or_row; return_array_or_row; [if_not_found]; [match_mode]; [search_mode])

Explanation of Function Parameters

a) Required

• lookup_value – the value the function searches for, e.g., a cell
• lookup_array_or_row – the range where the lookup value is searched. Can search left-to-right or right-to-left. Order does not matter
• return_array_or_row – the range from which the corresponding value is returned

b) Optional – useful for certain scenarios, saving time and formula size

• if_not_found – optional parameter to define the return value if the lookup value is not found (no need to wrap the function with ISERROR as with predecessors; using this parameter increases clarity)
• match_mode – optional parameter to specify the type of match (exact, approximate). This parameter exists in the predecessors too, but here it’s optional. If not specified, exact match is assumed.
• search_mode – optional parameter to specify search direction. The default setting (if not specified) is searching from the first column or row to the last. You can adjust this if needed.

Summary of XLOOKUP Advantages vs VLOOKUP/HLOOKUP – One Function to Rule Them All

XLOOKUP allows simple searching of values in table rows and columns. Previously, to search for values in columns or rows, you had to manage 2 functions – VLOOKUP and HLOOKUP – and choose based on whether you searched in rows or columns. Now, only 1 function is sufficient.

Main advantages of XLOOKUP over VLOOKUP and HLOOKUP include:

• Exact and approximate match: XLOOKUP can search for an exact value or the nearest value (higher or lower) without the need to sort data. VLOOKUP/HLOOKUP supports approximate match only with sorted data and requires the match type argument to be correctly set.
• Searching values left-to-right and right-to-left: Unlike VLOOKUP, which searches only left-to-right, XLOOKUP allows searching in any direction. This facilitates working with tables where columns are not arranged logically and eliminates the need to rearrange data.
• Replacing error values with custom messages or numbers: XLOOKUP has the if_not_found parameter, which allows defining a custom output if the lookup value does not exist. With VLOOKUP and HLOOKUP, you typically combine the function with IFERROR or IFNA, which increases formula complexity and reduces clarity.
• Easy combination with other functions for multiple criteria: XLOOKUP can be effectively combined with functions like IF, FILTER, or INDEX, enabling searches based on multiple criteria. With traditional functions, such combinations are more complex and less clear.

Thanks to the new upgrade, XLOOKUP greatly simplifies data lookup and minimizes the risk of errors when working with large or dynamic tables.

Example of Using XLOOKUP in Excel – 3 Tasks and Various Scenarios

We will demonstrate the function using an example shown in the screenshot. It is a simple customer list where the unique identifier is CustomerID, which we will use to search the table.

Notice that the CustomerID column (customer identifier) is placed at the end – to show that XLOOKUP can search right-to-left (unlike VLOOKUP)

We have 3 tasks:

1. Find the email of the customer with CustomerID = 164
2. Find the CompanyName of the customer with CustomerID = 99999, and if it does not exist, display a custom message “!! Customer does not exist !!”
3. Show the first and last email of the customer by CompanyName = “Friendly Bike Shop” (this company has 4 customers)

Solution for Task 1 – Find Email by CustomerID

This is probably the simplest and most common scenario – we search the table and want to return a value from one column (EmailAddress) based on another column (CustomerID). Here we practice the basic syntax.

Solution for Task 2 – Find CompanyName and display a custom message if not found

In the second task, we practice the scenario where the value (CustomerID = 99999) does not exist in the data. In this case, we want to handle the return value and display a custom message. Handling N/A values is also among the most common scenarios.

Solution for Task 3 – Show First and Last Email by CompanyName = “Friendly Bike Shop”

This scenario is not something you will use frequently, but it nicely demonstrates what the new function can do. Here we practice the last argument of the search mode (from start vs from end).

We can see the solution in the screenshot. For clarity, I filtered the table for CompanyName = Friendly Bike Shop, and we can check that using the last argument, we can control whether the function searches from the top or bottom. In the first case, it returned the first value encountered from the top; in the second case, the first value encountered from the bottom.

Recap of XLOOKUP Usage Procedures, Excel Download

• • Ensure that lookup and return ranges are of the same length
  • Combine XLOOKUP with ISNA or IFERROR for error handling
  • Use named ranges for dynamic lists

• Like older functions, XLOOKUP returns the first match it finds (if multiple identical values exist in the lookup range)

The XLOOKUP function is a robust and flexible tool for professional work with Excel data. It allows efficient value lookup, replacement of error outputs, and working with multiple criteria.

You can download the Excel file with source data and all tasks here: >> XLOOKUP_practising_tasks_en.xlsx

1. Microsoft documentation, XLOOKUP function [on-line]. [cit. 2025-11-25]. Available from: https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929