Tutorial – XLOOKUP in Excel – Shiksha Online

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

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
• Syntax of XLOOKUP in Excel
• Examples of XLOOKUP
  • Fetch a Lookup Value
  • Fetch the Entire Record
  • Error Handling
  • Lookup in Multiple Ranges

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

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. 

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.

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.

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.

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

The formula used here will be –

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

lookup_value = K3

lookup_array = A2:A13

return_array = B2:B13

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

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.

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

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.

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.

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

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

Drag horizontally to fill in the remaining cells.

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