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:

  1. by filtering the Excel Table
  2. 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”)


#”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

%d bloggers like this: