How to use XLOOKUP function In Excel – A Complete Guide

Most of the excel users are familiar with the VLookup Function. How to use it, What are the advantages and limitations of the VLookup Function. Microsoft has introduced a new powerful function as a successor to VLookup which is the XLOOKUP function. In this article let us learn about the XLOOKUP function in excel in detail with examples.

What is XLOOKUP Function in Excel

XLOOKUP is one of the new functions in the excel functions list. This is an advanced version for functions like VLookup and HLookup. This functions can help to overcome certain limitations that the earlier look up functions have. If you are a regular user of look up functions and are using the complex functions of Index and Match then you will definitely like the flexibility XLookup function offers.

Syntax of XLOOKUP Function in Excel

The Syntax of Excel XLookup Function is as follws

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

XLookup Function has 6 arguments out of which 3 are mandatory and 3 are optional.

Arguments of XLookup Function

Lookup_Value : This is the value to search for. This is a Mandatory argument. If this argument is blank, XLookup returns details of blank cells it finds in the lookup array.

Lookup_array : This is the range or array where to search for the lookup value. Required argument.

Return _Array : The array or range from which you want to get the details based on the position of the look up value.

If_Not_Found : Optional argument. The value to return in case the lookup value is not available in lookup array. You can specify what message you want XLookup to return. If this is not specified the function returns #N/A error by default.

Match_Mode : This argument helps to specify the type of match. Optional argument.

• This argument accepts 4 values.
  • 0 – Returns Exact Match. If the look up value is not available, the function returns a #N/A. This is also the default option.
  • -1 – Returns the Exact Match. If Exact match is not available, the function returns the next smaller item.
  • 1 – Returns the Exact Match. If Exact match is not available, then the function returns the next larger item.
  • 2 – To do a match using wildcard characters like *, ~ , ? etc.

Search_Mode : Optional argument. This argument helps you to choose the way you want to search the lookup value in lookup array.

• This argument also accepts 4 values.
  • 1 – Search for the Lookup value from top to bottom in the lookup array. This is also the default option.
  • -1 – Search for the Lookup value from bottom to top in the lookup array.
  • 2 – Performs a Binary search on the data. However, Data needs to be sorted in Ascending order.
  • -2 – Performs a Binary search on the data. However, Data needs to be sorted in Descending order.

Understanding Binary Search

According to Microsoft, this is for advanced users. Binary search is much faster than a regular search, but works correct only on sorted data. It is a specially designed algorithm that finds the position of a lookup value in the sorted array by comparing it to the middle element of the array.

Note : Ensure to sort your Data in Ascending or Descending order when you are using the search mode argument. If the Data is not sorted, then the function will return incorrect data.

Examples of XLookup Function in Excel

Let us now see the different ways of using XLookup function in excel using some practical examples. This will help you understand how XLookup is more superior to VLookup and eliminates the need of using Index and Match Functions.

Example 1 – The basic XLookup Function

To Start with let us use XLookup function in Excel in the simplest form. Suppose you have Data of multiple companies along with the industry and sales details quarter wise in a tabular format. Now you want to find out sales of Quarter 2 for a particular company. See the below image.

For Example, you want to get the Q2 Sales for Company Contoso Pharmaceuticals, then the formula would be as below.

=XLookup(H3,B3:B15,E3:E15)

In the above function we have used only the mandatory arguments. The lookup value is the company Name (in Cell H3), Lookup Range is the list of company names (from B3 to B15) and Return value is from range Q2 Sales ( from B3 to B15).

In plain words, XLookup function looks for “Contoso Pharmaceuticals” in the range B3 to B15 and returns the corresponding Value from range E3 to E15.

Example 2 – Left Lookup in Excel using XLOOKUP

Unlike Vlookup, XLookup have the lookup and return arrays as different arguments. An advantage of having it in this way is you can do a look up to the left as well. Where as, this is one of the limitations in Vlookup, which works as a right function.

See the below image. There is a small change to the sample data in the above image. We moved the Company Details to Column E and Q2 Sales to Column C. Now let us see how XLookup works in this scenario. See the below image

In this example we see that we are having the return array towards the left side of lookup array. However, XLookup function returned the corresponding value based on the lookup value.

Example 3 – Lookup and Return a Range/ Row based on Lookup Value

Another feature of XLookup function in excel is, it’s ability to return a range of data. This means, XLookup can return more than one value corresponding to the lookup value. This do not require any syntax changes or extra efforts.

Taking the same data as an example, If you want to fetch all the details for a particular company instead of only Q2 Sales. To do this you need to input the range in the Return_Array argument instead of a single row or column. In this scenario, XLookup function will be as follows.

=XLookup(H3,B3:B15,C3:E15)

Instead of a single column, entire range of C3:E15 is passed in the return array argument. See the below Image to understand.

Note that you cannot delete the data in the cells that are a part of the array. You can only delete the formula in the cell which you have entered. In the above example, we have entered the formula in Cell H2 and you can delete the formula from H2. Upon clearing the data in H2 entire result will also get deleted.

Example 4 – Look up Vertically or Horizontally

Till now we have been using two different functions VLookup and HLookup to perform a vertical or horizontal look up respectively. XLookup can do the same that these two different functions used to do. However the only difference would be with the lookup and return arrays we provide to XLookup.

• For Vertical Look Up – You need to use Columns for Lookup and Return array arguments.
• For Horizontal Look Up – You need to use rows for Lookup and Return array arguments.

The examples seen till now demonstrate the usage of XLookup function for Vertical Lookup. Using the same data from the above example, I want to get the industry in which the company is in. However, this time data is in horizontal format. In this case the formula will be

=XLookup(C7,B2:G2,B3:G3)

See the below image to understand the usage of XLookup function in excel for Horizontal lookup.

Example 5 – Lookup Value not Found

When using the lookup functions if the lookup value is not found the function returns an error. This used to be handled by using the appropriate error handling criteria as required. However, XLookup function in excel comes with an inbuilt error handling option.

The fourth argument in the function “IF Not Found” gives the flexibility to display the text of your choice in case the look up value is not found. You can write the XLookup function as below.

=XLOOKUP(H3,B3:B15,E3:E15,”Company Not Found”)

The text “Company not found” is displayed when the company name which we are looking for is not available in the company list. See the below image to understand it better. We are searching for Apple INC sales details. However Apple Inc is not available in the company List. Hence XLookup returns “Company Not Found” message instead of #N/A error.

Example 6 – Find Approximate Match using XLOOKUP

See the below example to understand how you can use XLookup to perform an approximate match. The Match_Mode argument is used to perform an approximate match. Let us use the same example. In this case I want to grade a company based on the total sales done for both Q1 and Q2. The grading should be done as per the second table in the below screenshot. The formula will be as follows

=XLOOKUP(E4,$K$5:$K$8,$L$5:$L$8,,-1)

Note the -1 (which is for the Match Mode Argument) tells XLookup to look up for the value and find an approximate match in case an exact match is not available. For example : Company Adventure Works the total sales for both the quarters is $857,117. XLookup is not able to find the exact match and now it looks for an approximate match. Since we are using -1 it searches for the next smaller match which is $800,000. Hence it returns the Grade as “C“.

For the same data if we use 1 instead of -1 the grades changes as seen in the below image. XLookup returns #N/A as we have not assigned any grade for the total sales over 13 million.

Example 7 – Find Last Match using XLOOKUP

One of the other advantages of XLookup is that you can do a search from bottom to top and return the last value. You need to use the Search_Mode argument for the same and set to -1. See the below image to understand it better.

Availability of XLOOKUP function in Excel

Since this is one the new functions that is released recently, it is not available in all the versions of Excel. Currently XLOOKUP function is available to the users of Office 365, Excel Web Version and and Excel 2021. So, if you are using previous functions of excel (2003 – 2019) you will not be able to use the XLookup function. As of now no one have any idea if this function will be available in previous versions.

Conclusion

This article explains and focuses on the basic uses of XLOOKUP. There are other advanced ways you can use XLOOKUP (with combination of various other functions, multiple lookups etc.) . I would write another article on using XLOOKUP the advanced way and also the advantages it has over VLOOKUP.

What Do you think of XLOOKUP function in Excel. Do You use the XLOOKUP function frequently. Share in the comments.

Do Not Forget to Share this article with your Friends !!!