XLOOKUP vs. VLOOKUP

The XLOOKUP vs. VLOOKUP

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…

I used to do that with VLOOKUP, but not anymore.

Let’s do a deep dive on how each one of them 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

=XLOOKUP(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

◾ SEARCH_MODE → This is optional. It determines the search direction.

➡️ 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.

➡️ How does the VLOOKUP function work?

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

and then retrieve values based on specific criteria.

➡️ Syntax

=VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,COL_INDEX_NUM,[RANGE_LOOKUP])

Let’s break down this syntax:

◾ LOOKUP_VALUE → This is the value you want to search for in the first column of the table

◾ TABLE_ARRAY → This is the range of cells containing the data

◾ COL_INDEX_NUM → This is the column number in the table from which to retrieve the value

◾ RANGE_LOOKUP → This is optional. TRUE for an approximate match or FALSE for an exact match.

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

It can be used for lookup up a precise value

For e.g. SUMIFS is a great function, but sometimes you don’t want to sum values in a range…

sometimes you just want to pull in an exact value.

That’s when you’ll want to use a VLOOKUP function.