XLOOKUP Part 1 – The evolution of VLOOKUP and INDEX MATCH

XLOOKUP becomes the new function for looking up items in a table or range in different ways, making data handling in Excel more efficient. It includes the main features of VLOOKUP and INDEX/MATCH, adding different possibilities for finding the data you're looking for.

In this article you'll find the basic and most common uses of XLOOKUP. If you're looking to use it for more advanced tasks such as searching within matrices (by row and column), summing over variable ranges, or finding the closest value, we recommend checking out XLOOKUP Part 2 – Advanced functions.

General syntax:

XLOOKUP("what you want to look up", "where you want to look", "where the result is located", [if not found], [match mode], [search mode])

[ ]: Optional parameters.

Contents:

• Searching from right to left
• Built-in IFERROR
• More than one column as the result

1. Searching from right to left

The same as a VLOOKUP, but without the limitations. You can search within a matrix even if the search term is to the right of the result.

XLOOKUP("what you want to look up", "where you want to look", "where the result is located")

2. Built-in IFERROR

You don't need to add the IFERROR function, since it's already built into XLOOKUP. If the value you're looking for isn't found, you just enter the value to display, and that way you won't get errors in your formulas.

XLOOKUP("what you want to look up", "where you want to look", "where the result is located", "if not found")

3. More than one column as the result

How many times have you used VLOOKUP to try to look up a term but couldn't bring back more than one result at a time? XLOOKUP is the solution.

XLOOKUP("what you want to look up", "where you want to look", "where the result is located (can be multiple columns)", ["if not found"])