In this post, we’ll talk about a very useful function in Excel called XLOOKUP. If you work with big lists of data and need to find specific information, XLOOKUP helps you save time by searching for that data automatically.

What is XLOOKUP?

XLOOKUP is an improved version of VLOOKUP. It allows you to search for a value in one column and find a related value in another column, but it’s more flexible and easier to use. Unlike VLOOKUP, XLOOKUP can search both vertically and horizontally, and it doesn’t require the result column to be on the right side.

How to Use XLOOKUP

XLOOKUP follows a simple formula:

Here’s what each part means:

• what-you're-looking-for: The value you want to find. For example, a product ID.
• where-to-look: The range of cells where you want to search. It could be a list of product IDs.
• what-to-return: The range of cells where the corresponding data (like prices) is found.
• [if-not-found]: Optional. If the value isn’t found, you can show a custom message like "Not Found" (similar to IFERROR).
• [match-mode]: Optional. Choose whether to search for an exact match, close match, or wildcard match.
• [search-mode]: Optional. You can choose to search from the top, bottom, or use a binary search (faster for sorted data).

Step-by-Step Example:

Let’s say you have a list of product IDs in column A and their prices in column B. You want to find the price of a product with an ID in cell D1.

1. Write the formula:

• D1 is the product ID you're searching for.
• A is the column with the product IDs.
• B is the column with the prices.
• "Not Found" is a custom message that will appear if the product ID isn’t in the list.

1. Result:
   Excel will return the price for the product listed in D1 or show "Not Found" if the ID isn’t there.

Common Mistakes

Here are some things to avoid when using XLOOKUP:

• Forgetting the search ranges: Make sure the columns you’re searching in and returning values from match the structure of your data.
• Missing the ‘not found’ message: If you don’t include the [if-not-found] argument, Excel will return an error if the value isn’t found. Adding a custom message makes your data cleaner.
• Misplacing the return column: XLOOKUP lets you choose any column to return data from, so make sure you’ve selected the right one.

Helpful Tips

• Lock your ranges: Use dollar signs ($A$2:$B$100) to lock the range if you copy the formula to other cells.
• Named ranges: You can name your data ranges (e.g., “ProductData” for IDs and “PriceList” for prices) to make the formula clearer:

• Use match modes: XLOOKUP lets you look for exact matches, close matches, or use wildcards. For example, you can search for partial names in a list by using the 2 match mode (wildcards).

Conclusion

XLOOKUP is a powerful and flexible tool in Excel. It does everything VLOOKUP does but with more options and less hassle. Whether you’re searching for data in big tables or just pulling small bits of information, XLOOKUP can help you find what you need quickly and easily. Try it on your own data to see how much easier it makes your work!