Google Docs spreadsheet ARRAYFORMULA & Filter ( no dragging!)

I discussed the Google Docs MMULT function and its usage in my previous article now in this one I will be discussing a proper usage of that.

If you have been using your GDocs spreadsheets for a while (which I assume you should  😉  ) then you must have encountered situations where you need a FILTER formula which has to be filtering the full column. You must have been taking the easier way till now I assume, i.e. dragging the FILTER function till the bottom. Its easy and solves the purpose, but if your data is dynamic and you want the new row to automatically use the FILTER it becomes a bit of a problem. As you can see below, since we did not drag the FILTER function till the value 80 the function could not be used

FILTER in Google Docs spreadsheet

Now I will explain you how easily you can make use of MMULT and ARRAYFORMULA to achieve this task  😯

I will recommend you read my article on MMULT before proceeding to get a better understanding.

As shown above you have an array of “Source” data and you want to automatically pick the values if there is any value in

Step1:

Based on your input value, first you need to create a [1 X N] array (e.g [0 0 0 0 1 0 0 0 0].  The one represents your input value which needs to be selected. This can be achieved by using the formula

=  ARRAYFORMULA ( SIGN (  B2=TRANSPOSE(E2:E10)  ) )

  • SIGN is used so as to convert the TRUE/FALSE values to 1 and 0.
  • ARRAYFORMULA is used so that the full transpose array is picked up

Step 2:

Now our next task is to return the required filtered value.  Since we need to return the values from Column F, we will do a Matrix multiply of the above [1 X N ] to the column F  [N X 1 ] array. So our formula becomes :

=  ARRAYFORMUL ( MMULT ( SIGN (  B2=TRANSPOSE(E2:E10)  ) ;  F2:F10 )

There will be a matrix multiplication performed and the value from column F corresponding to the Input will be returned.  Please NOTE :  F is the column from which you want the results to be returned

Now if you see below, when I enter value 70 in input, I automatically get the result of filter from columnF !!  That is it!

Step 3:

If you have noticed, there are some unnecessary 0’s in the places where you don’t have an input value. You can easily remove them using IFERROR function explained here already.

The formula now becomes :

=  ARRAYFORMULA ( IF(isblank(B2:B10);iferror(1/0); MMULT ( SIGN ( B2:B10=TRANSPOSE(E2:E10) ) ; F2:F10 )))

And thats it!!!!

Tip  :  For example purposes I used a limited array (e.g F2:F10), but you can use an open ended array (e.g F2:F), just make sure that the matrix is compatible.

Let me know via comments if you need any assistance .

-Abhi

Ref: Gif files created using MakeGif.com