Tutorial – XLOOKUP in Excel – Shiksha Online

Tutorial – XLOOKUP in Excel – Shiksha Online

6 mins read1.1K Views Comment
Rashmi
Rashmi Karan
Manager - Content
Updated on Mar 3, 2023 13:40 IST

The article talks about an advanced and flexible lookup function, XLOOKUP.

2022_05_XLOOKUP-in-excel-1.jpg

The lookup and reference functions help us find values ​​within an Excel workbook, spreadsheet, or range. To achieve the desired result, it should meet the criteria in the Excel function. We have already covered VLOOKUP and HLOOKUP previously; now let’s move on to the extended lookup function which is XLOOKUP.

The XLOOKUP function is the advanced lookup function that combines the functionalities of both VLOOKUP and HLOOKUP functions. It has no limitations and searches for a value in a row or column of a table. It then returns the corresponding value in a table. 

XLOOKUP is one of the most useful features of Excel and offers the easiest way to search for specific entries in a set of cells. A unique feature of XLOOKUP is that it doesn’t need the lookup value to be in the first column or row of a table as we have seen for VLOOKUP and XLOOKUP. Also, it returns a default value if it doesn’t find a match, instead of #N/A. Excel’s XLOOKUP function allows you to search not just one, but multiple entries.

Content

Use Cases of the XLOOKUP Function

Below is a brief summary of the most common use cases of the XLOOKUP Function

  • Single Entry Search
  • Search for multiple entries at once
  • Row search
  • Search for exact results and for the next minor or major inconsistent entries 
  • Using placeholder characters in search criteria
Tutorial – VLOOKUP in Excel – Shiksha Online
Tutorial – VLOOKUP in Excel – Shiksha Online
VLOOKUP, or vertical lookup, is one of Excel’s most commonly used functions for data analysis. This function belongs to the Search and Reference group, which has other search functions in...read more
How to Import Text Files to Excel
How to Import Text Files to Excel
Managing massive volumes of data is tricky, especially when those are on your text files. Moving data to Excel for better calculations and presentations is an efficient way to handle...read more
How to Use HLOOKUP in Excel
How to Use HLOOKUP in Excel
The HLOOKUP in Excel enables finding data in a dataset horizontally. HLOOKUP stands for Horizontal and searches for a value in the top row of a table. The function then...read more

Must Explore – MS Excel Courses

Syntax of XLOOKUP in Excel

To work effectively with XLOOKUP, you must first become familiar with the individual parameters that make it up. Every function in Excel has a specific syntax that should meet certain parameters. Even a small change in the data can lead to an incorrect result or an error message. The syntax that applies to the XLOOKUP function is as follows – 

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

Parameters

  • value: The entry you are looking for; can be composed of text, numbers, or placeholders. This is a mandatory argument.
  • lookup_array: The range or array in which we need to search the required information. Another mandatory argument.
  • return_array: The range in which we need to return the desired information. It is also a mandatory argument.
  • if_not_found*: If Excel doesn’t find a valid match, it can return a text or value that you provide. Otherwise, the program will display the message “#N/A”. Optional argument.
  • match_mode*: Compare mode allows you to specify a type of match. This is an optional argument
  • 0: Exact match. If none is found, returns #N/A. This value is the default.
  • -1: Exact match. If none is found, it will return the next smallest element.
  • 1: Exact match. If none is found, it will return the largest item.
  • 2: A wildcard match when *,? and ~ has a special meaning.
  • search_mode*: This parameter determines where the search should start (“1” for the first element and “-1” for the last) or you start a binary search.
  • 1: Performs a search starting with the first element. This value is the default.
  • -1: Performs a reverse lookup starting from the last element.
  • 2: Performs a binary search that depends on sorting in ascending order. If not sorted, invalid results will be returned.
  • -2: Performs a binary search depending on the sort in descending order. If not sorted, invalid results will be returned.

Examples of XLOOKUP

1. Fetch a Lookup Value

We will use the below data set to understand how XLOOKUP works. In this data, we have Types of vehicles, Number of vehicles, and Cost of vehicles. We need to find out the cost of a vehicle type Public Safety SUV from the range and then return its cost. 

2022_05_Fetch-a-Lookup-Value-1.jpg
2022_05_Fetch-a-Lookup-Value-2.jpg

For this example, we will be using the below formula –

=XLOOKUP(E3,A2:A11,C2:C11)

Where,

lookup_value = E3

lookup_array = A2:A11

return_array = C2:C11

 It doesn’t include the match_mode argument, because, for this data, Excel will produce an exact match.

2022_05_Fetch-a-Lookup-Value-3.jpg

As you can see, the XLOOKUP function fetches the exact price of a Public Safety SUV, which is  ₹1,190,600 as mentioned in the original data set.

Weighted Average in Excel – Shiksha Online
Weighted Average in Excel – Shiksha Online
This tutorial will teach you how to calculate a weighted average, also known as a weighted mean.
How To Calculate Percentage In Excel
How To Calculate Percentage In Excel
Percentages are a very powerful tool for data analysis. They can be used to make data more concise, easier to understand, and more comparable. In this tutorial, we will learn...read more
How To Create A Pivot Table In Excel?
How To Create A Pivot Table In Excel?
A pivot table is a powerful tool in Excel that allows you to summarize and analyze large amounts of data. It is essentially a way of reorganizing and manipulating data...read more

2. Fetch the Entire Record

Let’s look out for the marks of students in the below data set. Here I want to check the entire score of a particular student, Chaitali.

2022_05_Fetch-the-Entire-Record.jpg

Here w would need to use the subject names beside the dataset so that XLOOKUP and look for values in the given dataset.

2022_05_Fetch-the-Entire-Record-2.jpg

The formula used here will be –

=XLOOKUP(J2,A2:A13, B2:B13)

lookup_value = K3

lookup_array = A2:A13

return_array = B2:B13

2022_05_Fetch-the-Entire-Record-3.jpg

You can just drag and fill in the rest of the cells to get the complete scores.

2022_05_Fetch-the-Entire-Record-4.jpg

3. Error Handling

We will use the first dataset (Example 1) to apply the if_not_found argument. XLOOKUP formula also has the error handling function and the fourth argument in the XLOOKUP function is [if_not_found]. In this instance, you can specify what you need if the lookup value can’t be found. Let’s check if the below dataset has any entries for MUVs.

2022_05_Error-Handling.jpg

To check for the cost of MUV, we will use the formula

=XLOOKUP(E3,A2:A11,C2:C11,”Not Available”)

Where,

lookup_value = E3

lookup_array = A2:A11

return_array = C2:C11

if_not_found = Not Available

2022_05_Error-Handling-2.jpg

Now that the dataset doesn’t have the value for MUV, the XLOOKUP function will go as coded and will mark Not Available for MUVs.

2022_05_Error-Handling-3.jpg

Read MS Excel Tutorials

4. Lookup in Multiple Ranges

We have a new dataset in which we will look out for value in the multiple ranges. The [if_not_found] argument allows using the nested XLOOKUP formula. So in the below dataset, we have 2 tables on the same sheet and we will look for the data we need.

2022_05_Lookup-in-Multiple-Ranges.jpg

Gear up, we have a complex nested formula coming up to figure out the score achieved by Sonali.

2022_05_Lookup-in-Multiple-Ranges-2.jpg

=XLOOKUP(A13,A2:A10,B2:B10,XLOOKUP(A13,J2:J10,K2:K10))

2022_05_Lookup-in-Multiple-Ranges-3.jpg

Drag horizontally to fill in the remaining cells.

2022_05_Lookup-in-Multiple-Ranges-4.jpg

Note: XLOOKUP is not available in Excel 2016 and Excel 2019. However, if someone else used XLOOKUP in a newer version, you can use it on your system.


Top Trending Articles in MS Excel:

Most Useful Excel Formulas | Min Max Functions in Excel | Average Functions in Excel | Introduction to MS Excel | Financial Modelling in Excel | MS Excel interview questions | Sum Function in Excel | Trim Function in Excel | Pivot Table in Excel | Percentage in Excel | Vlookup in Excel | Median Function in Excel | Types of Charts in Excel | Count Function in Excel | MS Excel Vs. Google Sheet | Remove Duplicates in Excel | Create Graph in Excel

FAQs

What is a mandatory argument?

Mandatory/Compulsory/Required argument is the range or array of cells that need to be evaluated by the given criteria. Without a mandatory argument, the indicated formula or function won't work. e.g. =XLOOKUP(E3,A2:A11,C2:C11) Criteria: (Compulsory argument) It suggests that the given range is mandatory to be used in the function for further calculations.

What is an optional argument?

Optional arguments allow you to omit arguments for certain parameters. They are denoted with square brackets. For example in the formula - =XLOOKUP(value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) [if_not_found], [match_mode], [search_mode] - These parameters are optional arguments.

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio