The XLOOKUP Power

The XLOOKUP Power

Learn about one of the most powerful functions in Excel

The XLOOKUP function in excel is something I use pretty much anytime I open an excel file…

Let’s do a deep dive on how it works

➡️ How does the XLOOKUP function work?

The XLOOKUP function allows you to search through a range of data 👀

and then retrieve values based on specific criteria.

➡️ Syntax

=VLOOKUP(LOOKUP_VALUE,LOOKUP_ARRAY,RETURN ARRAY,[IF_NOT_FOUND],[MATCH_MODE])

Let’s break down this syntax:

◾ LOOKUP_VALUE → This is the value you want to search for in the search array

◾ LOOKUP_ARRAY → This is the range of cells containing the data, including the column or row where the lookup value is located

◾ RETURN_ARRAY → This is the range from which to retrieve the value

◾ IF_NOT_FOUND → This is optional. It allows you to specify a value or action to take if no match is found

◾ MATCH_MODE → This is optional. It allows you to specify the match mode for the lookup

➡️ What are some important things to note about the XLOOKUP function?

XLOOKUP can look left or right in a vertical data set or up or down in a horizontal data set.

You can use only the 3 required arguments with XLOOKUP.

XLOOKUP uses arrays so if data is added to the data set, XLOOKUP can still work.

It can handle errors without using another function such as IFERROR.

XLOOKUP can return an array not just a single value.

It can make use of wildcards.

XLOOKUP can perform reverse searches when the search_mode is -1.

===

There’s a lot more to say about the XLOOKUP function…

What would you add?

Let us know in the comments below 👇

PS: Don’t forget to grab this infographic in high res via the link