Dynamic Array – XLOOKUP
What we wanted in the last article (https://bit.ly/2sQCWvL), was done in mid-January: Microsoft has activated the Dynamic Array for all Office 365 users, not just for "Insiders". In the following articles we will describe these new functions. For today, XLOOKUP.
As usual, we start from a problem and try to identify the elements to solve.
Problem: Data table and extracting information in various situations.
Access to the work file:
Problem 1. We have a data form, where when choosing the product code (Code_P) from F2 we must obtain the product name (Den_P) and the value of the product (Value). As the data in the table are positioned we will use the classic VLOOKUP function. Thus, in F3 and F4, respectively, we will have:
= VLOOKUP (F2; A2: C11; 2; 0), respectively = VLOOKUP (F2; A2: C11; 3; 0)
The VLOOKUP function is limited by the restrictions it has:
- the known information (the product code) must always be in the first column, so the requirement will always be to the right (product name and value);
- the formula must contain FALSE or 0 for an exact search.
In this case, we pose a second problem.
Problem 2. If the known information is Den_P, how do we extract the requirement Cod_P, to the left?
Until now, we used the INDEX and MATCH functions (the solution from F8).
= INDEX (A2: C11; MATCH (F7; B2: B11, 0), MATCH (E8, A1: C1; 0))
Let's continue to see the use of the XLOOKUP function. We resume the problem data in a new spreadsheet (XLOOKUP). I transformed the data table through Format as Table. It's not just a design element. I just want to use the benefits of structured references.
Let's take them one at a time:
Lookup_value - the known element (Cod_P or Den_P)
Lookup_array - the data area. If in the VLOOKUP and HLOOKUP functions the area contained the column containing the known element (mandatory the first column) as well as the other columns where we wanted to get the result (mandatory in the right) now lookup_array refers only to the column containing the known element.
Return_array - the column from which we want to get the information (we are not interested in its position - left or right)
= XLOOKUP (F2; Table3 [Cod_P]; Table3 [Den_P]) - solving at Den_P
= XLOOKUP (F2; Table3 [Code_P]; Table3 [Value]) - solving at Value
For the second problem, where I initially used INDEX and MATCH, the solution also comes from XLOOKUP, because with this function the restrictive elements disappeared.
= XLOOKUP (F7; Table3 [Den_P]; Table3 [Cod_P]) - solving the second problem, Cod_P
The following items are not required.
[if_not_found] - if the known item is not in the list, it will return # N / A or you may display a message: "No data!" (Attention! Text is quoted or not, if you use the graphical interface of the function - fx).
[match_mode] - As I said before, a restriction on VLOOKUP was the exact search (0 or FALSE), implicitly value 1 (TRUE), approximate value. At XLOOKUP, the default value is finally on the exact search.
0 = exact match (default)
-1 = exact match or next smallest
1 exact match or next largest
2 = wildcard match.
[search_mode] - can be used for search type and direction
1 = search from first (default)
-1 = search from last
2 = binary search ascending order
-2 = binary search descending order.
For the optional part it would be good to solve a problem related to: the known element is approx. We don't know exactly the value. We approximate it to 500. We want to find the code for this value.
= XLOOKUP (F18; Table3 [Value] Table3 [Cod_P] 0; 1; 1)
For the next time the UNIQUE function (choosing the unique values from a list) and the SORT function (sorting the extracted elements).
As usual, we start from a problem and try to identify the elements to solve.
Problem: Data table and extracting information in various situations.
Access to the work file:
Problem 1. We have a data form, where when choosing the product code (Code_P) from F2 we must obtain the product name (Den_P) and the value of the product (Value). As the data in the table are positioned we will use the classic VLOOKUP function. Thus, in F3 and F4, respectively, we will have:
= VLOOKUP (F2; A2: C11; 2; 0), respectively = VLOOKUP (F2; A2: C11; 3; 0)
The VLOOKUP function is limited by the restrictions it has:
- the known information (the product code) must always be in the first column, so the requirement will always be to the right (product name and value);
- the formula must contain FALSE or 0 for an exact search.
In this case, we pose a second problem.
Problem 2. If the known information is Den_P, how do we extract the requirement Cod_P, to the left?
Until now, we used the INDEX and MATCH functions (the solution from F8).
= INDEX (A2: C11; MATCH (F7; B2: B11, 0), MATCH (E8, A1: C1; 0))
Let's continue to see the use of the XLOOKUP function. We resume the problem data in a new spreadsheet (XLOOKUP). I transformed the data table through Format as Table. It's not just a design element. I just want to use the benefits of structured references.
Let's take them one at a time:
Lookup_value - the known element (Cod_P or Den_P)
Lookup_array - the data area. If in the VLOOKUP and HLOOKUP functions the area contained the column containing the known element (mandatory the first column) as well as the other columns where we wanted to get the result (mandatory in the right) now lookup_array refers only to the column containing the known element.
Return_array - the column from which we want to get the information (we are not interested in its position - left or right)
= XLOOKUP (F2; Table3 [Cod_P]; Table3 [Den_P]) - solving at Den_P
= XLOOKUP (F2; Table3 [Code_P]; Table3 [Value]) - solving at Value
For the second problem, where I initially used INDEX and MATCH, the solution also comes from XLOOKUP, because with this function the restrictive elements disappeared.
= XLOOKUP (F7; Table3 [Den_P]; Table3 [Cod_P]) - solving the second problem, Cod_P
The following items are not required.
[if_not_found] - if the known item is not in the list, it will return # N / A or you may display a message: "No data!" (Attention! Text is quoted or not, if you use the graphical interface of the function - fx).
[match_mode] - As I said before, a restriction on VLOOKUP was the exact search (0 or FALSE), implicitly value 1 (TRUE), approximate value. At XLOOKUP, the default value is finally on the exact search.
0 = exact match (default)
-1 = exact match or next smallest
1 exact match or next largest
2 = wildcard match.
[search_mode] - can be used for search type and direction
1 = search from first (default)
-1 = search from last
2 = binary search ascending order
-2 = binary search descending order.
For the optional part it would be good to solve a problem related to: the known element is approx. We don't know exactly the value. We approximate it to 500. We want to find the code for this value.
= XLOOKUP (F18; Table3 [Value] Table3 [Cod_P] 0; 1; 1)
For the next time the UNIQUE function (choosing the unique values from a list) and the SORT function (sorting the extracted elements).
