How To Use The XLOOKUP Function in a Microsoft Excel Spreadsheet?

Posted on

Microsoft Excel Blog Banner

There are many Functions available in Microsoft Excel. There are Functions, combination of Functions, more than what we can think of. Remember when we were looking for a Function to lookup and retrieve data within a Table, and we used to use the VLOOKUP function. VLOOKUP Function that I described in a previous post of mine and you can find here. Well now the successor of the VLOOKUP Function is the XLOOKUP Function. It is named like this because it can search bother vertically and horizontally. It allows us to search for an item in a Range or Table and return the matching result.

The XLOOKUP Function has Five Arguments, where the first three are required and the last two are optional. The first three necessary arguments are the following:

  • lookup_value: What we are looking for.
  • lookup_array: Where to find it.
  • return_array: What to return

How To Use The XLOOKUP Function in a Microsoft Excel Spreadsheet

In the image below, I have entered Data into a Table. This Table contains information about TV Shows. I have the Rank number of the TV Show among with the Network that has created the TV Show and the percentage of Viewers watching the TV Shows. The Rank of each TV Show depends on the percentage of Viewers. Say, now that we want to find out all the information that is related with the Show that is in the Rank 96. Information such as the Title of the Show, the Network that created the Show and the Viewers of the Show.

How To Use The XLOOKUP Function in a Microsoft Excel Spreadsheet

In the image below, I have selected the Formulas tab, and then from the Function Library area of the ribbon, I have selected the Lookup & Reference Category as shown. Once the Lookup & Reference category is selected the drop-down menu appears with all the available Functions, which in my example is the Function XLOOKUP.

What does the XLOOKUP Function do? It Searches A Range Or An Array For A Match And Returns The Corresponding Item From A Second Range Or Array. By Default, An Exact Match Is Used.

How To Use The XLOOKUP Function in a Microsoft Excel Spreadsheet

The syntax of the XLOOKUP Functions is the below:

XLOOKUP(lookup_value;array;return_array;if_not_found;match_mode;search_mode)

Once the XLOOKUP Function is selected, the Function Arguments dialog box appears as shown in the image below:

The XLOOKUP Function is separated in the following arguments:

  • Lookup_value: Is The Value To Search For. It Searches A Range Or An Array For A Match And Returns The Corresponding Item From A Second Range Or Array. By Default, An Exact Match Is Used.
  • Lookup_array: Is the array or range to search. It searches a range or an array for a match and returns the corresponding item from a second range or array. By default an exact match is used.
  • Return_array: Is the array or range to return. It searches a range or an array for a match and returns the corresponding item from a second range or array. By default, an exact match is used.
  • If_not_found: Returned if not match is found. It searches a range or an array for a match and returns the corresponding item from a second range or array. By default, an exact match is used.
  • Match_mode: We specify how to match lookup_value against the values in a lookup_array. It searches a range or an array for a match and returns the corresponding item from a second range or array. By default, an exact match is used.

How To Use The XLOOKUP Function in a Microsoft Excel Spreadsheet?

For my example in this post the Arguments that are been used are the following:

  • Lookup_value: Is the value 96, which is the Rank in the Cell G2 and we are looking for.
  • Lookup_array: Is the Column A, which contains all the Ranks of the Titles of the Shows and we are searching.
  • (TOP_100_SHOWS_OF_2018_2019__TOTAL_VIEWERS[Rank])
  • Return_array: Are the Columns B, C and D, where we want the results from, and we are looking in. (TOP_100_SHOWS_OF_2018_2019__TOTAL_VIEWERS[[Title]:[VIEWERS (000)]])
  • If_not_found:
  • Match_mode:

How To Use The XLOOKUP Function in a Microsoft Excel Spreadsheet?

Once we have filled in all the Arguments, we just press the Ok button, located at the bottom right corner of the Function Arguments and see the results. In the image, according to our Arguments, the result at Rank 96, which we were looking to find the relative Data, the Title is 48 Hours, the Network is CBS and the Viewers are 4,475. Three Results, in one.

If we double click on the Function in the actual Cell, we can see the whole Function in the Formula Bar as shown in the image below.

How To Use The XLOOKUP Function in a Microsoft Excel Spreadsheet?

Below you can check out the video describing How To Use The XLOOKUP Function in Microsoft Excel?

Don’t Forget To Subscribe To My YouTube Channel!!!

YouTube Channel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s