Buzz
In the recent update, Microsoft has introduced and added some new functions to Excel with the aim of enhancing and supplementing new features for users. Among those functions, the XLOOKUP function is one of the names mentioned in this latest update.
The XLOOKUP function will replace the VLOOKUP function.
1. What is XLOOKUP?
The XLOOKUP function is a new function, which is the upcoming solution that Microsoft will officially introduce to replace the VLOOKUP function to address some limitations that VLOOKUP is facing. Additionally, XLOOKUP is also used to replace the HLOOKUP function, for example, XLOOKUP can look leftwards, default to exact match, and allows specifying the range as a cell instead of column numbers, whereas VLOOKUP is not as user-friendly and flexible.
Currently, the XLOOKUP function is only available to Insider program users, so anyone using Insiders can update, experience, and use this XLOOKUP function immediately. In the near future, Microsoft will soon finalize and release this function to all Office 365 users.
2. How to use XLOOKUP function in Excel
1. Syntax of XLOOKUP Function
The syntax for the XLOOKUP function is as follows:
The XLOOKUP function will have 5 arguments, where only the first 3 arguments are mandatory, and the remaining 2 arguments are optional, specifically:
- lookup_value: The value to search for
- lookup_array: The range to search in
- return_array: The range where users want to return the result
- [match_mode]: The option for value matching mode during search
- [search_mode]: The option for search mode
Moreover, please take note of the following information:
- lookup_value: It can be a single value or an array of values you want to search for
- lookup_array and return_array: Need to match in size
Thus, with this new XLOOKUP function, the basic arguments are similar to those of the VLOOKUP or HLOOKUP functions. Therefore, if you are familiar with the syntax and usage of these two functions, you will definitely find it easier to approach and grasp XLOOKUP.
2. How to Use XLOOKUP Function
XLOOKUP Function Can Search Leftwards
As known, the limitation of the VLOOKUP function is that it can only return results from a column located to the right of the first column in the lookup table. Similarly, the HLOOKUP function can only return results from a row located below the first row in the lookup table. You can overcome this limitation by combining with other functions such as the CHOOSE function, or for more advanced solutions, you can combine INDEX and MATCH functions for instance.
With the XLOOKUP function, this limitation has been addressed. You can see a specific example below for better understanding. For instance, you can base your search on item names using their prices:
XLOOKUP Function Can Search from the End to the Beginning (search last to first)
Using the XLOOKUP function provides flexibility in data searching, for example, you can search from bottom to top. This searching method also addresses the limitations encountered by the VLOOKUP or HLOOKUP functions.
With the fifth argument of the function set to -1 (Search last-to-first).
So, for the same Item 1, the formula in cell E3 with the fifth parameter set to 1 (Search first-to-last) will search from the beginning to the end in the lookup_array (similar to the VLOOKUP function).
Next, the formula in cell E5 with the fifth parameter set to -1 (Search last-to-first), so the function will perform a search from the end to the beginning of the lookup_array (this is something that the VLOOKUP function cannot do in a straightforward manner).
XLOOKUP Function Returns an Array of Values
XLOOKUP function helps you return an array of values, for example:
The formula in cell I4 is:
=XLOOKUP(H2,A4:A7,A4:F7)
So, the function will search for the value in cell H2 within the range from A4:A7, and the return range here will be from A4:F7. Similarly, you will do the same with the formulas in cells I6 and I8.
XLOOKUP Function Concatenates Search Values
XLOOKUP function also allows you to concatenate strings in the search value, search range, and result range. When you want to search with multiple different conditions, you can concatenate those values to perform the search. See the illustrated example below:
XLOOKUP Function Searches for an Array of Values
XLOOKUP function aids users in searching through arrays of values. Check out the example below for a clearer understanding:
In the given example, the UNIQUE function is employed to eliminate duplicate values, thereby returning a unique list.
When searching within an array of values, the resulting output is an array of values arranged vertically (meaning it comprises multiple rows but only one column). Here are some key insights:
- XLOOKUP function can return an array of values horizontally (i.e., along rows in Excel, consisting of one row and multiple columns).
- It can also return an array of values vertically (meaning multiple rows and one column).
- This returned array can either be one-dimensional or two-dimensional, but in the latter case, one dimension must have a size of 1. The XLOOKUP function cannot return an array of values comprising multiple rows and columns (≥2 rows and ≥2 columns). For more specific details, refer to the example above (cell I12).
Utilizing other functions combined with XLOOKUP
XLOOKUP function also enables you to combine functions, utilizing nested functions flexibly.
For example, you can use the XLOOKUP function to return an array of values as described in the image below. You can calculate the total quantity of items occurred in the last occurrence.
Flexible string concatenation in the returned result of XLOOKUP function
XLOOKUP function helps you concatenate flexibly in the returned result. You can see that the returned result is not just numbers but will also combine, applying the function to return an array of values combined with Text strings nested within the returned result. For example, in the image below, you will see the flexible string concatenation in the returned result to view the quantity and total amount occurred first and last of the occurred items.
Using nested XLOOKUP within XLOOKUP
You can see the illustrated example below to understand the usage of nested XLOOKUP within XLOOKUP. Suppose you need to calculate the total quantity of items counted from the first occurrence of item C until the last occurrence of item C.
XLOOKUP overcomes the issue of inserting rows, columns in VLOOKUP and HLOOKUP
When users want to insert an additional column (with VLOOKUP) or insert an additional row (with HLOOKUP), using the VLOOKUP or HLOOKUP function will result in incorrect output. However, if you use the XLOOKUP function, this issue can be overcome.
For example:
- If you want to insert an additional column between columns A and B, the result in D3 when using the XLOOKUP function will not change. Whereas cell D2, when using the VLOOKUP function, the result has changed leading to an incorrect result.
- Similarly, if you use the HLOOKUP function and insert an additional row between row 7 and 8 -> the result in cell H6 when using the XLOOKUP function will not change. Whereas cell H5 when you use the HLOOKUP function -> the result has changed leading to an incorrect result.
The XLOOKUP function in Excel is a new function, inheriting to overcome the limitations that both VLOOKUP and HLOOKUP have. With a range of features and usage along with the examples above somewhat help you see the advantages that the XLOOKUP function brings. Experience and start using this function right away if you are using the Insider version for Office 365!
