The new version of Excel has a Filter formula, which is so awesome that it directly formulates the function of filtering
For example, on the left is the employee data, and now the H2 cell is the department condition
We hope that when we enter the marketing department, we can filter out all the corresponding data
1. Filter single condition
The usage of Filter is:
=Filter(Filter result, filter condition)
The result of this filter is: A:D column data region
The filter condition is column A, find the value of the H2 cell, so
The formula we just need to enter is:
=FILTER(A:D,A:A=H2)
When the H2 cell value is updated, the result can also be updated accordingly:
2. Filter multiple conditions
If we say, we need to meet both conditions at the same time
We have to screen the department, and there is no data on the payment of wages
In this case, an additional E condition is added, and both conditions need to be met at the same time:
So we need to add a condition, which we need to connect with a multiplier sign, and the input formula is:
=FILTER(A:D,(A:A=H2)*(E:E=""))
Column A is the value of cell H2, and column E is empty data, and column A:D will be filtered out
3. Filter single condition, multiple judgment values
If we say, we can enter 2 conditions in the department, and then filter out the corresponding values
Look for cell H2 in column A
If you look for H3 cells in column A, you can get data as long as one of these two conditions is met
So the formula we enter is:
=FILTER(A:D,(A:A=H2)+(A:A=H3))
Note that a plus sign is required between the two conditions, which means that one of them is sufficient
Have you learned about this function formula? Try it out!