Share via

How to return any specific column details from a range using FILTER formula ?

Anonymous
2023-09-27T05:57:44+00:00

I'm using Filter formula : =FILTER(A4:BB166,J4:J166=A2,"").

It will return all the column (A to BB) details as per the filter condition.

but I want only some column ( For E.g. - Column A, B, D, F, L, P, AA & AS) details from the range (A4:BB166) as per my filter condition.

How I can do that ?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2023-09-28T01:53:33+00:00

    Hi,

    Try this formula

    =choosecols(FILTER(A4:BB166,J4:J166=A2,""),1,2,4,6,12,16,27,45)

    Hope this helps.

    10 people found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. riny 20,870 Reputation points Volunteer Moderator
    2023-09-28T04:39:41+00:00

    Please enlighten me with regard to "Pick".

    1 person found this answer helpful.
    0 comments
  2. riny 20,870 Reputation points Volunteer Moderator
    2023-09-27T06:59:47+00:00

    Easiest would be if you have an empty row above the table headers where you can enter zeros and ones for columns to be excluded and included. Alternatively, insert a row above the table, but that will of course change the references in you original formula.

    Let's assume have an empty row 2 available. Enter the number 1 in columns A, B, D, F, L, P, AA & AS on that row and zeros in all other columns through BB or just leave them blank.

    Now, change the formula to:

    =FILTER(FILTER(A4:BB166,J4:J166=A2,""),A2:BB2)

    Only the columns where you entered 1 on row 2 will be displayed.

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2023-09-28T01:45:54+00:00

    > ... Easiest would be if you have an empty row above the table headers where you can enter zeros and ones

    One does not really want to enter a complicated helper row...

    I wouldn't do it like this, but the easier idea is...

    Image

    0 comments
  4. Anonymous
    2023-09-27T07:16:59+00:00

    Thanks Riny_van_Eekelen

    0 comments