excelfilterdynamic

Excel FILTER with dynamic conditions

Can someone please kindly explain how I filter the first column based on specific criteria (column and data) please.

1 2 3 4 5 6 7 8 9 10
a
b x z x z y z
c y x x x
d z x x y x x y y z
e x y x
f x x y x
g x x y x y
h z z x x y
I
j z x x y x

My dynamic criteria would be a cell referencing the columns (1-to-10) and another cell specifying "x, y or z".

From the example dataset above, with the 2 criteria's being column 6 and the data "x", I would like the resulting filter in another cell list "c" and "d" from the first column.

I can create excel filters to display the correct outputs from column A based on a fixed column and dynamic data criteria, and I can also make it filter the correct column data based on a dynamic column header. But I can't seem to join the two.

=IFERROR(FILTER(A1:A12,B1:B12=B16),"") B16 refences "x", B1:B12 being the fixed column

=FILTER(B2:K12,B1:K1=B15) B15 references 5

I have read a few other questions around dynamic filters, but none that I have seen that have the dataset in my format.

Solution

  • Here is the solution:

    enter image description here

    [C16]=FILTER(A2:A11,INDEX(B2:K11,0,MATCH(B17;B1:K1;0))=B16,"")

    B16 contains the second criterion, B17 contains the first criterion.

    INDEX(B2:K11,0,MATCH(B17;B1:K1;0)) resolves the problem you trapped. MATCH results the column number where the sought id is. INDEX returns the required column.

    Alternatively

    [C16]=TOCOL(IF((B2:K11=B16)*(B1:K1=B17),A2:A11,1/0),2)