How to use XLOOKUP in Excel (with examples)

Are you still using VLOOKUP or HLOOKUP in Excel? It’s time to upgrade and learn how to use XLOOKUP in Excel which provides a powerful and more flexible lookup function. Many Excel users haven’t yet explored how to use XLOOKUP, even though it simplifies complex lookups and eliminates some of the limitations found in the original lookup functions. In this guide, you’ll learn what makes XLOOKUP better, how it works, and how to use it with real-world examples.

Here are some advantages of using XLOOKUP or VLOOKUP/HLOOKUP:

• XLOOKUP doesn’t require the lookup data to be sorted.
• XLOOKUP defaults to an exact match when looking for data which improves accuracy.
• XLOOKUP allows you to specify a custom error message if the lookup value isn’t found whereas VLOOKUP and HLOOKUP require the addition of the IFERROR function.
• The lookup criteria can be located in any column within your data whereas VLOOKUP/HLOOKUP requires the criteria to be located in the first column.

This newer lookup option is available to all Microsoft 365 subscribers but isn’t supported in older Excel versions, such as Excel 2019. For detailed information and the latest updates, see Microsoft’s official XLOOKUP function guide.

Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode)

lookup_value	The lookup_value is the value you are using as your lookup e.g. ID number, value.
lookup_array	The lookup_array identifies where the lookup value can be found. E.g. Column F
return_array	The return_array identifies where the returning value can be found. E.g. Column M
if_not_found	The if_not_found allows you to determine what happens if the lookup_value is not found within the lookup_array. This is an optional inclusion.
match_mode	The match_mode allows you to determine if you want the lookup_value to be an exact match with several allowances or do you want to use a wildcard match.

Create a simple XLOOKUP function

1. Open Microsoft Excel.
2. Open an existing worksheet where you may want to use the XLOOKUP, or create a simple file as shown below:

3. In this example, you will see a list of staff including their Staff ID, Name, and Department details. You will see at the top there is a section to enter the Staff ID and then have Excel lookup the name and department associated with that staff member.
4. Click in cell C2 which is where we want the name displayed for the staff member.
5. Select the Formulas tab and choose the Lookup & Reference book from the Function Library:

6. Select XLOOKUP from the list.
7. The Function Arguments window will appear with the XLOOKUP statement ready to go:

8. Click in the Lookup_value field. This is where you need to identify where the value is that you are want to find within the table. In this case select cell B2 which is where we will type the staff ID we want to look up.
9. Click in the Lookup_array field. This is where you identify where the lookup values are located. In this case, where are all the staff ID’s listed in the worksheet. Select cells B5 to B22:

10. Click in the Return_array field. This is where you identify where the returning details are listed. In this case, where are all the staff names listed in the worksheet. Select cells C5 to C22.

As an extra feature you could change cell B2 to feature a simple drop down list of options to choose from. This would avoid users having to manually type in entries. Click here for instructions on how to create an Excel drop down list.

Using If_not_found

The If_not_found argument provides a way to handle situations where the lookup value is not found in the search range. Instead of returning an error, like #N/A, you can specify a custom message or value to be returned if the lookup doesn’t find a match.

This feature improves the user experience by making your spreadsheets more user-friendly and reducing the need for additional error-handling functions like IFERROR. For example, you might use if_not_found to display a message like “Record not found” instead of leaving the cell with an error message.

1. Click in the If_not_found field. Enter whatever text you would like to display in place of a possible error message:

2. The Match_mode field is provided if you need to change the type of lookup that is performed. By default, the XLOOKUP function performs an exact match lookup. For this exercise we can leave this empty to use the default matching option.
3. Click OK.
4. You should see the name associated with the Staff ID displayed:

5. Repeat the function to create the XLOOKUP to look up the department details.

Using Match_mode

The match_mode option allows you to control how the function matches the lookup value with the values in the search range. There are four modes available:

• exact match (0),
• exact match or next smaller item (-1),
• exact match or next larger item (1), and
• wildcard match (2).

The exact match mode is the default, ensuring the function only returns a result if an exact match is found. The other modes are useful for more flexible searching, such as finding the closest match or using wildcards to match patterns in text.

Some Examples

Now that you’ve created, possibly your first XLOOKUP, here are some examples to give you some ideas of how to use XLOOKUP within different scenarios.

• Staff Database: This is exactly what we’ve just seen in action in the above exercise. XLOOKUP can quickly find staff or employee details such as name, department, and contact information.
• Sales and Inventory Management: XLOOKUP can help match product IDs or names with their corresponding sales figures, inventory levels, or prices, for better decision-making and inventory control.
• Financial Analysis: XLOOKUP can assist to analyse financial data by retrieving specific information like transaction amounts, dates, or categories, making it easier to track expenses or revenues.
• Student Grades and Performance Tracking: XLOOKUPcan be utilised to retrieve student grades, attendance records, or exam scores from a student database, making the process of monitoring progress much quicker and easier.

If you found this information helpful or have any questions about using this lookup function, feel free to leave a comment below. Sharing your experiences or challenges can help others learn too!