Learn To Use XLOOKUP [2 Full Examples!]

It’s time we introduce you to the new member of the modern function family of Excel – the XLOOKUP function.

XLOOKUP is the successor to the conventional VLOOKUP, INDEX & MATCH, LOOKUP, and the HLOOKUP function.

You must have heard the meme floating on the internet these days that says.

There are two types of people in the Excel world; the type that masters VLOOKUP & XLOOKUP. And the type that hovers about the ones who master VLOOKUP and XLOOKUP!

Which type are you? If you have been ‘type two’ all this time, this article is sure to pave your way to ‘type one’!

XLOOKUP Explained

Through XLOOKUP, Excel has offered a one-in-all solution to all the VLOOKUP problems of Excel users.

It allows you to look up data, both horizontally and vertically, to the above and the left.

Users can define multiple criteria and can seek a whole row or column of data as the return value instead of a single value only.

This powerful successor of the VLOOKUP function is all that Excel users have been pleading for the last three decades.

We teach using XLOOKUP on our MS Excel intermediate course given how easy it is to use.

excel-promo-2

Syntax

The syntax of the XLOOKUP function looks as below.

 

A little too long to decipher? Let’s break it down into individual arguments to make better sense of it.

Arguments

• Lookup_Value – the first argument represents the value to look for in a given dataset.
• Lookup_array – the second argument refers to the data range where the value is to be looked for.
• Return_Array – the third argument refers to the array/range from where the value that is to be returned.
• If_not_found – the fourth argument is an optional one that refers to the value to be returned if the desired value is not found. If the lookup value doesn’t exist in the data set and the ‘if_not_found’ argument is omitted, Excel returns the #N/A error.
• Match_Mode – the fifth argument is also optional. It refers to the match type to be performed by Excel:

• • Exact Match: Under this option, Excel looks out for an exact match of the lookup value and returns the #N/A error if not found. To set the match mode to the exact match, set the fifth argument to ‘0’.
  • Exact or next smaller: Under this option, Excel looks out for an exact match of the lookup value. If an exact match is not found, Excel returns the next smaller value to the lookup value. Set up ‘-1’ as the fifth argument to put the match mode to ‘Exact or next smaller.
  • Exact or next larger: Under this option, Excel looks out for exact matches of the lookup value. If an exact match is not found, Excel returns the next larger value to the lookup value. Set up ‘1’ as the fifth argument to put the match mode to ‘Exact or next smaller’.
  • Wildcard character: Setting up the fifth argument to ‘2’ puts the match mode to the wildcard character match.

If the fifth argument is omitted, Excel, by default, sets it to 0; the exact match mode.

• Search_Mode – the sixth and the last argument to the XLOOKUP formula is an optional one. It guides the direction of search and can be set to four modes:

• • Search First to Last: Set this argument to ‘0’ or leave it omitted to perform the search for the lookup value from first to last.
  • Search Last to First: Set this argument to ‘-1’ to perform the search for the lookup value from last to the first.
  • Binary Search on Ascending Data: Setting this argument to ‘2’ performs a binary search on ascendingly sorted data.
  • Binary Search on Descending Data: Setting this argument to ‘-2’ performs a binary search on data sorted in descending order.

Return Value

The XLOOKUP function returns the value from the return array that matches the lookup value and the supporting specified criteria.

Functions Library

To find the XLOOKUP function from the Functions Library, go as follows.

 

Version of Excel

The XLOOKUP function is an advanced function of Excel that is only available to the users of Office 365. Users of the previous Excel versions from 2010 to 2019 will not be able to access the XLOOKUP function.

Pro Tip!

Is there some way out you can use the modern-day XLOOKUP function without being an Office 365 user? Yes, through Excel For Web!

Log on to your Microsoft OneDrive account and launch Excel online to have access to the latest functions of Excel.

OneDrive is a great tool, you can even use it to recover Unsaved Files in Excel.

How is XLOOKUP of use?

It took Microsoft around three decades to come up with a function as wholesome and robust as the XLOOKUP.

This function is a successor of the VLOOKUP function and is primarily designed to offer a solution to all the problem areas where VLOOKUP and other functions failed.

Why would you want to use XLOOKUP? Consider the data below.

The data consists of employee names alongside the number of items sold by each of them.

To the right side of the image, is a key that grades the performance of employees based on the number of units sold by them.

If you want to grade the performance of all the employees as ‘Good’, ‘Average’ or ‘Bad’ – could this be done with a single function?

Yes, the XLOOKUP function. See below.

Seems like magic? There are many more ways how XLOOKUP can ease your job.

• Use XLOOKUP to fetch a value, array, or range from a given data set.
• Use XLOOKUP to fill grids based on a given criterion in the blink of an eye.
• Use XLOOKUP to create an Excel dashboard by selecting only the data that you need.

XLOOKUP V/s. VLOOKUP

What differentiates XLOOKUP from its predecessor, the VLOOKUP? Below are the four main areas where XLOOKUP outstands the contemporary VLOOKUP function.

1.      Vertical and Horizontal Lookup

The VLOOKUP function is designed to perform a vertical lookup only. Take a quick look at the example below to find the difference between a horizontal and a vertical lookup.

With VLOOKUP, you can find the any sector’s revenue for a particular state (say State B’s fiscal sector revenue) by performing VLOOKUP.

However, you cannot find any state’s revenue for a particular sector.

This is because it requires a horizontal lookup of values which the VLOOKUP function fails to offer.

Using the XLOOKUP function, you can look up the data both ways.

This is essential a way to compare your columns, but lets you do it with more than two!

2.      Search Mode & Match Mode

The XLOOKUP function offers two optional arguments that help users to define the match mode.

For instance, do you want Excel to return an exact match from the lookup array or an approximate match (higher or smaller values)?

Similarly, search mode allows users to tell if the lookup value needs to be searched for, from the left of the lookup array or the right.

VLOOKUP fails to offer both the above-said features, making it rigid to use.

3.      If Not Found Argument

The very common #N/A error of VLOOKUP comes to the screen when Excel fails to find an exact match of what you’re looking for.

While XLOOKUP does no different, it allows users to replace the nasty looking #N/A with any value/dialogue of their choice. You might even choose to leave it vacant.

4.      Lookup Array and Return Array can be separately identified

Take a quick look at the data below:

The above data is not in a row but in two stacked tables.

XLOOKUP can search for a value from such scattered data if the dimensions are compatible. See below.

However, VLOOKUP cannot handle such datasets.

For VLOOKUP to work on such data, the data must be arranged together in a row as below.

XLOOKUP Example 1 – Basic Exact Match

It’s time we delve into examples that demonstrate the uses of the XLOOKUP function.

The first example in this article covers the basic function of the XLOOKUP function – seeking an exact match.

The image below contains data for the employees of an organization.

 

For each employee, the available details include the first name and the employee ID; however, the last name is missing.

Another dataset, as shown below, includes the employee IDs and the respective last names of employees.

Now what, copy-pasting?

Do note that the sequence of employee IDs in the second table do not match the sequence in the first table.

Copy-pasting the correct last name against each first name might take you ages.

Using the XLOOKUP function under the exact match mode, bringing these two lists together is only about a minute.

Begin writing the XLOOKUP function as follows.

 

• The first argument is set to B2, which contains the lookup value – the employee ID against which the last name is required.
• The second argument includes a reference to the lookup_array where the lookup_value is to be looked for. Column H contains the employee ID against the last names and is selected as the lookup_array.
• The third argument consists of the return_array, from where the value is to be returned. We want the last name of employees which are situated in Column I.
• The fourth argument of ‘if not found’ is omitted.
• The fifth argument is omitted as we want an exact match of the employee ID. Excel would have set it to ‘0’ by default.
• The sixth argument is omitted as we want Excel to perform the search from first to last. Upon being omitted, Excel by default sets it to ‘0’.

And you’re good to go. Press ‘Enter’ to see the following results.

Drag and drop to yield the same results for the remaining employees.

 

XLOOKUP Example 2 – Multiple Values

One main factor that distinguishes XLOOKUP from VLOOKUP is its ability to return not only a single value, but a range of values.

This feature of the XLOOKUP function was much demanded and is of great use. Earlier, to have multiple values returned from a lookup range, multiple VLOOKUP functions were to be combined.

But the very advanced XLOOKUP function can do this in a single formula. See below how.

Let’s expand the example stipulated above to include further detail about employees, as shown below.

The above data now also constitutes the date of joining and leaving of each employee alongside their designation.

Can we fetch the last name, joining and leaving date, and designation of each employee to the first table all at once? With the XLOOKUP function, yes.

Compose the XLOOKUP function as follows:

 

While everything remains the same as the above formula, what has changed?

We have only changed the third argument, return_array from I2:I10 to I2:L10. The range I2:L10 includes four columns.

For each employee code (the lookup value) Excel would then return the value of all the corresponding columns from Column I to Column L.

Time to see this in action? See Below.

 

For each employee code, the XLOOKUP function returns multiple cell values from all the specified corresponding columns.

Don’t stop only there. Drag and drop the above function to the entire list to have your data sorted in only a second.

Point to Ponder: How is this different from the VLOOKUP function?

Can VLOOKUP not perform the above operation? The precise answer to this is, that a single VLOOKUP cannot. To fetch more than one value like the above example, you need to operate multiple VLOOKUP functions.

For instance, to fetch all the details for Employee Code BX12360, write the VLOOKUP function as follows:

 

Pay close attention to the last argument that specifies the ‘Column’ number of the table from where we want the return value. Here it is set up as ‘2’.

Excel would therefore return the value from the second column (Column I) of the specified table array of H2:L10, which contains Employee last names only.

What about the other values? The VLOOKUP functions need to be set up for each of them again and again with the last argument changing to the return value column number.

With the XLOOKUP, you can target as many return value columns as desired – that’s when you know how badly XLOOKUP was needed.

XLOOKUP Troubleshooting

There are a variety of errors the XLOOKUP function might pose while you continue to twitch it in different ways. Once you know what each error has to say, resolving it shouldn’t take you a great deal of effort.

#REF Error

The very annoying reference error is set forth by the XLOOKUP function when it is being operated in two or more workbooks at the same time.

This might be the case when the lookup array and the return array reside in one workbook, and the XLOOKUP is employed in another workbook.

That’s no big deal as Excel can handle that effortlessly.

However, for this to be done, both the workbooks must be simultaneously launched in the background.

If either of the workbooks is shut, Excel would end up returning the #REF error.

Simply open both the workbooks to get rid of the said error.

If the error still persists, use the formula auditing tools to help see why!

#VALUE Error

The #VALUE error posed by Excel is an indication that the lookup array and the return array specified by you are not compatible in terms of dimensions.

For example, if your lookup array has 7 rows but your return array is only 5 rows long, Excel would give back a #VALUE Error. See below:

Excel fails to return the PCT Code for Kiwi because of incompatible dimensions of both arrays. Change the return array to make it parallel to the lookup array to see the #VALUE error vanish away.

The return array is changed from B2:B6 to B2:B8 – compatible with the lookup array of A2:A8.

Absolute References

This is not a problem with Excel but a problem with the drag-and-drop function. See the example below.

Here we have set the formula for Cell C2 by setting the Marks key as the lookup array and the remarks key as the return array.

The formula runs perfectly well for the first cell. However, drag and drop it down to the entire list, and the results would distort as follows.

The #N/A error tells the lookup value is not found.

This is because when dragged and dropped, Excel automatically updates the cell references, and the lookup array and the return array have changed.

Turn the lookup array and the return array into absolute references by navigating the cursor to each cell reference in the formula bar and pressing the F4 Key. See how the results change.

Conclusion

In the fast-paced world of today, smart and swift functions like the XLOOKUP are a necessity. Going through the above article and understanding the functionality of each example therein can help you master the XLOOKUP function with sheer ease.