Introduction
I don’t like to criticise since I am by no means perfect but I just read an exchange from a web site in which a reader asked a question about filtering data and the answer provided was this array entered formula: =INDEX($C$2:$C$11,SMALL(IF(($B$15=$B$2:$B$11)($A$2:$A$11<=$B$14)($A$2:$A$11>=$B$13),MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11)),””),ROW(A1))). Moreover, that formula was entered in two separate cells so I suppose under some circumstances it might need to be entered three times, four times or even more.
That’s ludicrous when Excel can offer the following two solutions:
- by filtering the Excel Table
- by using Get & Transform to filter the table
The problem is this, find the products being sold or bought or used according to the following constraints:
Starting date: 1/1/2018
Ending date: 6/1/2018
Rep: John
The following screenshots help here:
1 Filtering the Excel Table
The data and the constraints
Filtering the date column: after 1st Jan and before 7th Jan … between
Filtering the Rep: Excel not only provides the filtering dialogue box but you can see here that it gives us a drop down list of the unique items in the column that we can choose from
Once these two filters have been applied, we see our solution:
2 Using Get & Transform (G&T)
With your cursor in the table, select the Data tab and click on From Table/Range
The Query Editor opens:
Click on the down arrow at the top of the Date column and select Date/Time Filters … Between:
Enter your starting and ending dates then click OK:
Now click on the down arrow of the Rep column, selecting Text Filter … Equals:
Enter the name to filter, John in this case, then click OK:
We have filtered everything now and here is the result:
The following screenshot and text illustrates the M code that the Query Editor generates for us and rather than going through all of the above, you could just open the initial query and create/paste this code into the editor and that will work too.
In full, the filtering code in G&T follows:
#”Filtered Rows” = Table.SelectRows(#”Changed Type”, each [Date] >= #datetime(2018, 1, 1, 0, 0, 0) and [Date] <= #datetime(2018, 1, 6, 0, 0, 0)),
#”Filtered Rows1″ = Table.SelectRows(#”Filtered Rows”, each [Rep] = “John”)
in
#”Filtered Rows1″
That’s it! It takes a lot longer to explain than it takes to do. However, would you really want to scratch you head over the array entered formula I showed you at the start of this page?
Download the Excel file from here lookup_multiple_values
Duncan Williamson
22nd March 2018
Excel with Excel Master 