Formula for filtering data

[Superlan]

Hello everyone!

I have a relatively simple question and I hope someone can help.

Can you please have a look in the enclosed sample excel file. I want to filter data, but not with the normal Filter function, but rather by typing the value which should be filtered in a specific cell. In my example, I would like to put a 1, 2 or 3 in cell C2 with the result that only the respective number is displayed and the other ones are hided (just what the normal filter function does).

I know that I can type the number I am looking for in the "Search" field when I click on the filter button of cell C3, but I need to put the number in a specific cell, which is C2 in this case.

I hope you understand what I mean. I really need help as I need to do this for a huge excel file.

Thanks!

[CAntosh]

One way to do this to put your complete list of data on another sheet and use a formula to only pull the entries that match C2. In the attachment, I moved your list of numbers to sheet2, then used the following formula in C4. It should be array-entered with Ctrl + Shift + Enter instead of the regular Enter:

=IFERROR(INDEX(Sheet2!$A$2:$A$13,SMALL(IF(Sheet2!$A$2:$A$13=$C$2,ROW(Sheet2!$A$2:$A$13)),ROW(1:1))-1),"")

Fill the formula down as far as you need to and it will only return the appropriate matches for your list on sheet2. Take a look at the attachment to see if it will work for you:

[Superlan]

That's brilliant! It helps me a lot, thank you!

Just one more question: If I have data in other columns as well (for example column D, E and F) and I want that those columns change accordingly, can you tell me the easiest way to do this?

[CAntosh]

If you want the other columns returned in the same order you can go with this in C4:

=IFERROR(INDEX(Sheet2!A$2:A$13,SMALL(IF(Sheet2!$A$2:$A$13=$C$2,ROW(Sheet2!$A$2:$A$13)),ROW(1:1))-1),"")

Alternatively, you can use this:

=IFERROR(INDEX(Sheet2!$A$2:$C$13,SMALL(IF(Sheet2!$A$2:$A$13=$C$2,ROW(Sheet2!$A$2:$A$13)),ROW(1:1))-1,COLUMN(A:A)),"")

If you only want to return specific columns or columns out of order, you can use the second formula with MATCH to get the right header, like so:

=IFERROR(INDEX(Sheet2!$A$2:$C$13,SMALL(IF(Sheet2!$A$2:$A$13=$C$2,ROW(Sheet2!$A$2:$A$13)),ROW(1:1))-1,MATCH(K$3,Sheet2!$A$1:$C$1,0)),"")

Whichever option you use should be array entered. It can then be filled right and down. See the attachment for examples of all three options:

[Superlan]

Wow you're really clever! I will try to use this for my excel file and see if it works.

Thanks again for your help!

[CAntosh]

Glad to help, good luck!

Keep in mind that the "-1" part of "ROW(1:1))-1)" exists because the actual data in the data table on sheet 2 starts in row 2. If your data were to start in row 3, then it should read "ROW(1:1))-2)"; if your data starts in row 10, it should be: "ROW(1:1))-9)", and so on. That subtraction figure should be one less than the starting row of your data.