In recent updates, Microsoft has added and brought users some new functions with the purpose of fixing and adding new features to Excel. In those functions, XLOOKUP function is one of the names mentioned in this latest update.
The XLOOKUP function will replace the VLOOKUP function
What is XLOOKUP?
XLOOKUP function is a new function, this is a solution that Microsoft will officially put into use to replace the VLOOKUP function to overcome some of the limitations that VLOOKUP is facing. In addition, XLOOKUP is also used to replace HLOOKUP, for example, XLOOKUP can look to the left, default detects exact results and allows to specify the range as cells instead of columns, while VLOOKUP does not. It is easy to use and not so flexible.
Currently, the XLOOKUP function only allows users of the Insiders program, so anyone who is using Insiders can update, experience and use this XLOOKUP function right away. Next time, Microsoft will soon complete and release this function for all Office 365 users.
Instructions for using the XLOOKUP function in Excel
1. The syntax of the XLOOKUP function
The syntax of the XLOOKUP function is as follows:
The XLOOKUP function will have 5 arguments, of which only the first 3 are required, and the following 2 are optional, namely:
– lookup_value: Is the search value
– lookup_array: The area (array) to search
– return_array: The area (array) where the user wants to return results
– [match_mode]: Is to select the value matching mode when searching
– [search_mode]: Is the search mode selection
In addition, please note the following information:
– lookup_value: It may be a value or an array of values you want to search
– lookup_array and return_array: Need to be matched in size
– [match_mode]: The default is exact search (0 – Exact match)
– [search_mode]: The default is search from beginning to end (1 – Search first-to-last)
Thus, with this new XLOOKUP function, basically the arguments are similar to those of VLOOKUP or HLOOKUP, so if you know the syntax and usage of these two functions, with XLOOKUP, you will easily more accessible and graspable.
2. How to use the XLOOKUP function
The XLOOKUP function can be searched on the left
As we know, the limitation of VLOOKUP is that the result of the return can only be a certain column to the right of the first column in the lookup table. Similarly, the HLOOKUP function can only return a single row below the first row in the lookup table. You can also fix it by using a combination of other functions such as CHOOSE, for example, or more advanced, you can combine. INDEX function and MATCH function such as.
With this limited XLOOKUP function fixed, you can see the specific example below for easy visualization. For example, you can base on the unit price to find the goods name:
The XLOOKUP function can search from end to first (search last to first)
Using the XLOOKUP function, you have the flexibility to search data, for example, you can search from the bottom. This search also fixes the limitations that VLOOKUP or HLOOKUP still have.
The function’s fifth argument will be -1 (Search last-to-first).
So, for the same Commodity 1, the formula is in the cell E3 If the fifth parameter is 1 (Search first-to-last), it will search from beginning to end in lookup_array (it is similar to VLOOKUP function).
Next is the cell formula E5 will have the 5th parameter of -1 (Search last-to-first) so the function will perform a search from the end to the top of the lookup_array (this way the VLOOKUP function cannot follow a normal way like this. ).
The XLOOKUP function returns an array of values
The XLOOKUP function helps you return an array of values, for example:
The formula in cell I4 is:
= XLOOKUP (H2, A4: A7, A4: F7)
Thus, the function will perform a search for the value in cell H2 in the range A4: A7 and the area that returns the result here will be the range from A4: F7. Similarly, you’ll work with the formulas in cell I6 and cell I8.
XLOOKUP function concatenates the search values
The XLOOKUP function also allows you to concatenate strings in the lookup value, search range as well as the result range. When you want to search with many different conditions, you can join those values to perform the search. See the example illustrated in the image below:
The XLOOKUP function looks for an array of values
The XLOOKUP function also helps users perform a whole array search. See the example below to understand and easily visualize:
In the above example, the UNIQUE function is used to eliminate duplicates and return the unique list.
When searching for an array of values, the result is a vertical array of values (that is, it will consist of multiple rows but only one column). So you will have some small sums are:
– XLOOKUP function can return 1 array of values horizontally (ie in rows in Excel, including 1 row and has many columns).
– The function can also return an array of values vertically (ie will have many rows and 1 column).
– This return array can be 1-dimensional, or 2-dimensional, but in these 2 dimensions, there must be 1 dimension with size 1. The XLOOKUP function will not return an array of values including multiple rows and multiple columns (> = 2 rows and> = 2 columns), you can see more detail through the example above (cell I12) above.
Use other functions in combination with XLOOKUP
The XLOOKUP function also allows you to combine functions together, flexibly combining functions.
For example, you can use the XLOOKUP function to return the whole array of values as described in the image below, you can calculate the total number of goods that last generated.
Flexible string concatenation in the return result of the XLOOKUP function
The XLOOKUP function helps you concatenate strings flexibly in the returned results. You can see that the return results are not just numbers but will combine, manipulating the function to return the array of values combined with nested Text strings in the returned result. For example, in the image below you will see the flexible nested, concatenated string in the results returns to see the amount of money and the first and last money arising of the goods.
Use XLOOKUP nested XLOOKUP
You see the example below will know using XLOOKUP nested XLOOKUP. Suppose you need to calculate the total quantity of goods from the time when the first C goods arises until the last C goods have arisen.
XLOOKUP fixes errors of inserting rows and columns in VLOOKUP and HLOOKUP
Using VLOOKUP or HLOOKUP when a user wants to insert a column (with VLOOKUP) or insert a row (with HLOOKUP), the result of the function will be wrong. But using the XLOOKUP function will fix this error.
– If you want to insert more columns between columns A and B, the results in D3 when using the XLOOKUP function will not be changed. In cell D2, when using the VLOOKUP function, the results have been changed and resulted in false results.
– Similarly, if using the HLOOKUP function, when you insert one more line between rows 7 and 8 -> the result in cell H6 when using the XLOOKUP function is not changed. And the H5 cell when you use the HLOOKUP -> function has been changed and results in a wrong result.
Jaw XLOOKUP Excel is a new function, a successor to overcome the limitations that both VLOOKUP and HLOOKUP have. With a series of features and usage along with the examples above, it helps you to see the advantages that the XLOOKUP function brings. Experience and use this function right away if you are using the Insider version for Office 365!