How to Use XLOOKUP in Excel | Quiz: Excel Quiz Test

Excel is a powerful tool that can help you perform various tasks, such as data analysis, calculations, reporting, and more. One of the most common and useful functions in Excel is the lookup function, which allows you to find and retrieve data from a table or range based on a given criteria.

There are different types of lookup functions in Excel, such as VLOOKUP, HLOOKUP, INDEX-MATCH, and XLOOKUP. In this blog post, we will focus on the XLOOKUP function, which is a new and improved version of the lookup functions. We will learn what is XLOOKUP, how it works, and how to use it in Excel with some examples.

What is XLOOKUP?

XLOOKUP is a function that was introduced in Excel in 2019. It is available only for Office 365 subscribers and Excel Online users. XLOOKUP is designed to replace the VLOOKUP and HLOOKUP functions, which have some limitations and drawbacks.

XLOOKUP is a versatile and flexible function that can perform various types of lookups, such as exact match, approximate match, wildcard match, reverse lookup, and more. It can also handle horizontal and vertical lookups, as well as lookups in multiple columns or rows.

The syntax of the XLOOKUP function is:

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

The arguments of the XLOOKUP function are:

• lookup_value: The value that you want to find in the lookup array. It can be a number, text, logical value, or a cell reference.
• lookup_array: The range or array that contains the lookup values. It can be a single row, single column, or multiple rows and columns.
• return_array: The range or array that contains the values that you want to return. It must have the same size and shape as the lookup array.
• [if_not_found]: (Optional) The value that you want to return if the lookup value is not found in the lookup array. It can be a number, text, logical value, or a cell reference. If you omit this argument, the function will return #N/A error.
• [match_mode]: (Optional) The type of match that you want to perform. It can be one of the following values:
  • 0 or omitted: Exact match. The function will return the value that exactly matches the lookup value. If there are multiple matches, the function will return the first one.
  • -1: Exact match or next smaller. The function will return the value that is equal to or smaller than the lookup value. This is useful for finding the closest match in a sorted lookup array.
  • 1: Exact match or next larger. The function will return the value that is equal to or larger than the lookup value. This is useful for finding the closest match in a sorted lookup array.
  • 2: Wildcard match. The function will return the value that matches the lookup value with wildcard characters. You can use ? to match any single character and * to match any sequence of characters.
• [search_mode]: (Optional) The direction that you want to search in the lookup array. It can be one of the following values:
  • 1 or omitted: First to last. The function will search from the first value to the last value in the lookup array.
  • -1: Last to first. The function will search from the last value to the first value in the lookup array. This is useful for finding the last match in the lookup array.
  • 2: Binary search ascending. The function will use a binary search algorithm to find the match in a sorted lookup array in ascending order. This is faster than the linear search, but it requires the lookup array to be sorted in ascending order.
  • -2: Binary search descending. The function will use a binary search algorithm to find the match in a sorted lookup array in descending order. This is faster than the linear search, but it requires the lookup array to be sorted in descending order.

How to Use XLOOKUP in Excel with Examples

Now that we have learned the syntax and arguments of the XLOOKUP function, let us see how to use it in Excel with some examples. We will use the following table of student data for our examples.

Example 1: Exact Match

Suppose we want to find the marks of a student whose name is “Divya”. We can use the XLOOKUP function as follows:

=XLOOKUP("Divya", A2:A11, E2:E11)

The function will return 80, which is the marks of Divya in column E.

Example 2: Approximate Match

Suppose we want to find the class of a student whose roll number is 4.5. We can use the XLOOKUP function with the match mode argument as -1 to find the exact match or next smaller value as follows:

=XLOOKUP(4.5, B2:B11, C2:C11, , -1)

The function will return 9, which is the class of the student whose roll number is 4 in column C.

Example 3: Wildcard Match

Suppose we want to find the section of a student whose name starts with “E”. We can use the XLOOKUP function with the match mode argument as 2 to perform a wildcard match as follows:

=XLOOKUP("E*", A2:A11, D2:D11, , 2)

The function will return B, which is the section of Esha in column D.

Example 4: Reverse Lookup

Suppose we want to find the name of a student whose marks are 70. We can use the XLOOKUP function with the search mode argument as -1 to search from the last value to the first value in the lookup array as follows:

=XLOOKUP(70, E2:E11, A2:A11, , , -1)

The function will return Farhan, which is the name of the student whose marks are 70 in column A.

Conclusion

XLOOKUP is a powerful and versatile function that can perform various types of lookups in Excel. It is easy to use and has many advantages over the traditional lookup functions, such as VLOOKUP and HLOOKUP. You can use XLOOKUP to find and retrieve data from a table or range based on a given criteria, and customize the type of match and the direction of search. You can also use XLOOKUP to handle horizontal and vertical lookups, as well as lookups in multiple columns or rows.
We hope you have learned how to use XLOOKUP in Excel step by step with this tutorial. If you have any questions or feedback, please let us know in the comments below. Thank you for reading and happy learning.