Santa Claus ... hoping to bring us the Dynamic Array
Santa Claus ... hoping to bring us the Dynamic Array
Careful! This article is for Microsoft Excel-dependent users.
Any Microsoft Excel-dependent user wants to be able to benefit from Microsoft's Dynamic Array promises by 2020. Don't you know about them?
You had problems with the VLOOKUP function because they didn't even look at the data to the left of the column, you wanted to extract data from a column faster and sort it automatically, you wanted to generate a Data Validation for a much faster dynamic range ? The solution is DYNAMIC ARRAY!
Let's take them one at a time...
It's been almost a year and a half since we got access to these functions through the Office Insider Program (Inside Channel). Unfortunately, they are only for Office 365 users, not for Office 2016 or Office 2019. Microsoft has said that they have passed tests, so we can have them (hopefully) from the beginning of the year on our computers.
XLOOKUP
Extracting information from a table involves using the VLOOKUP function. Function used in 99.9% of companies. From the following table we want to extract, using a form, the name of the event and the date of a certain month. To complicate things a bit, in the second form we want to find out the month of the event.
Problems. Extracting with Vlookup involves entering the formula of the "exact search" element FALSE or 0. Failing this means returning an erroneous result! This function cannot extract data to the left of the column in which the Lookup Value is found.
= XLOOKUP (F4; table2 [Month] table2 [Data])
Using the XLOOKUP function, the compulsory entry of the exact search element disappears, being implicitly entered in the formula. In this case, the formula involves the use of three main elements:
1. What we are looking for (F4 - May)
2. Where are we looking (Table2 [Month])
3. Where do we extract data (Table2 [Data])
For the second form we can no longer use the function Vlookup (restriction of data extraction left of the column in which the value is found). Here was the use of INDEX and MATCH.
Fortunately, we have all the XLOOKUP function that does not take this restriction into account.
= XLOOKUP (F11; table2 [Event] table2 [Month])
UNIQUE
We want to extract unique elements from a column by a formula - the process is thus automated when using Remove Duplicates.
= UNIQUE (Table1 [Agent])
The formula is only introduced in K2. The result is a Spill Range - multiple value extraction.
Deleting cell K2 results in deletion of the entire area of data obtained.
DATA VALIDATION
Entering a validation list, dynamic variant (it does not matter if I add or delete data from the source column) can now be made easier (without using the Offset function).
= $ K $ 2 #
We need to specify the source to select the start cell range and add #. Only that!
APRON
We work much better with sorted data. Now we have a function that fits our data properly.
= SORT (K2 #) or in a single formula = SORT (UNIQUE (Table1 [Agent]))
FILTER
We need to extract and display more results for one or more search values, but this filter will update automatically (compared to using Advanced Filter).
= FILTER (Table1 [[Data] [Category]]; Table1 [Agent] = S1)
In S1 we have the fill element. We select in the formula only the columns that we want to be displayed (must also include the searched item) - Table1 [[Date]: [Category]] and specify the column where the searched item is found - Table1 [Agent] = S1. Careful! It is a dynamic formula - when changing the agent's name, the extracted data is changed automatically.
So much for today.
From January we expect you at https://www.effectiveflux.com/articles/ with other useful information.
But it's also the end of the year!
We wish you health, happiness and a new year with fewer problems in formulas 😊!
Happy New Year!
Careful! This article is for Microsoft Excel-dependent users.
Any Microsoft Excel-dependent user wants to be able to benefit from Microsoft's Dynamic Array promises by 2020. Don't you know about them?
You had problems with the VLOOKUP function because they didn't even look at the data to the left of the column, you wanted to extract data from a column faster and sort it automatically, you wanted to generate a Data Validation for a much faster dynamic range ? The solution is DYNAMIC ARRAY!
Let's take them one at a time...
It's been almost a year and a half since we got access to these functions through the Office Insider Program (Inside Channel). Unfortunately, they are only for Office 365 users, not for Office 2016 or Office 2019. Microsoft has said that they have passed tests, so we can have them (hopefully) from the beginning of the year on our computers.
XLOOKUP
Extracting information from a table involves using the VLOOKUP function. Function used in 99.9% of companies. From the following table we want to extract, using a form, the name of the event and the date of a certain month. To complicate things a bit, in the second form we want to find out the month of the event.
Problems. Extracting with Vlookup involves entering the formula of the "exact search" element FALSE or 0. Failing this means returning an erroneous result! This function cannot extract data to the left of the column in which the Lookup Value is found.
= XLOOKUP (F4; table2 [Month] table2 [Data])
Using the XLOOKUP function, the compulsory entry of the exact search element disappears, being implicitly entered in the formula. In this case, the formula involves the use of three main elements:
1. What we are looking for (F4 - May)
2. Where are we looking (Table2 [Month])
3. Where do we extract data (Table2 [Data])
For the second form we can no longer use the function Vlookup (restriction of data extraction left of the column in which the value is found). Here was the use of INDEX and MATCH.
Fortunately, we have all the XLOOKUP function that does not take this restriction into account.
= XLOOKUP (F11; table2 [Event] table2 [Month])
UNIQUE
We want to extract unique elements from a column by a formula - the process is thus automated when using Remove Duplicates.
= UNIQUE (Table1 [Agent])
The formula is only introduced in K2. The result is a Spill Range - multiple value extraction.
Deleting cell K2 results in deletion of the entire area of data obtained.
DATA VALIDATION
Entering a validation list, dynamic variant (it does not matter if I add or delete data from the source column) can now be made easier (without using the Offset function).
= $ K $ 2 #
We need to specify the source to select the start cell range and add #. Only that!
APRON
We work much better with sorted data. Now we have a function that fits our data properly.
= SORT (K2 #) or in a single formula = SORT (UNIQUE (Table1 [Agent]))
FILTER
We need to extract and display more results for one or more search values, but this filter will update automatically (compared to using Advanced Filter).
= FILTER (Table1 [[Data] [Category]]; Table1 [Agent] = S1)
In S1 we have the fill element. We select in the formula only the columns that we want to be displayed (must also include the searched item) - Table1 [[Date]: [Category]] and specify the column where the searched item is found - Table1 [Agent] = S1. Careful! It is a dynamic formula - when changing the agent's name, the extracted data is changed automatically.
So much for today.
From January we expect you at https://www.effectiveflux.com/articles/ with other useful information.
But it's also the end of the year!
We wish you health, happiness and a new year with fewer problems in formulas 😊!
Happy New Year!
