Using a helpful thread (Excel Filter Function - choose certain columns as output) to successfully create a filtered table using selected columns I now need to add a second criterium to the filtered table.
I went for this option as it suited my needs most:
=FILTER(CHOOSE({1,2},B1:B7,D1:D7),$K$1:$K$7=$K$1)
I used a drop down field to determine "include" (so not a fixed value as per the above example). I now need to not just check the drop down but also exclude all lines which have a certain value in them ("C"). The column in question is both in the original array and in the filtered output table.
Background: The drop down selects the month I want to look at and I need to exclude all actions which are completed (i.e. have the letter "C" in that column).
As per the instruction on Microsoft's Webstie on the use of FILTER() you simply multiply the two booleans together:
=FILTER(CHOOSE({1,2},B1:B7,D1:D7),($K$1:$K$7=$K$1)*($C$1:$C$7 = "whatyouwant"))
Also, with HSTACK() we no longer need the CHOOSE():
=FILTER(HSTACK(B1:B7,D1:D7),($K$1:$K$7=$K$1)*($C$1:$C$7 = "whatyouwant"))