Results 1 to 6 of 6

FILTER function using 2D matrix table

Threaded View

  1. #1
    Registered User
    Join Date
    05-31-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2013
    Posts
    5

    FILTER function using 2D matrix table

    Hi All,

    Wondering if anyone could help with the below FILTER formula question.

    Based on the input in cell I2, I want to filter values in A2:A5 where the column header=I2 and the rows below the correct header are >0. In the screenshot below for example the INPUT is ROLE 1, so I would want the formula to search for 'ROLE 1' in B1:E1, then once it finds the correct column (in this case Column B), filter rows 2 to 5 (B2:B5) where the value is >0 and return the corresponding values in A2:A5.

    In this example the correct output in Apple and Watermelon.
    Screen Shot 2021-08-03 at 1.51.44 PM.png

    The formula =FILTER(A2:A5, B2:B5>0) solves the above in example when its just ROLE 1, but how could I get the formula to first check the role input in I2 to determine which column to filter >0?

    Thanks!
    Last edited by AliGW; 08-03-2021 at 02:33 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Optional FILTER in FILTER() Function
    By Yohnson in forum Excel General
    Replies: 21
    Last Post: 07-08-2021, 07:31 AM
  2. [SOLVED] Get Max and Min Value using Filter function
    By mikehk in forum Excel General
    Replies: 5
    Last Post: 03-06-2021, 11:43 AM
  3. Filter function in Office 365 - filter with designated field
    By Eric Tsang in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-28-2020, 10:55 AM
  4. [SOLVED] Help : Macro Function - unable to apply filter function after macro the file.
    By Shermaine2010 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-05-2013, 12:50 AM
  5. Help with the filter function.
    By justifiedcandy in forum Excel General
    Replies: 6
    Last Post: 08-19-2010, 02:04 PM
  6. [SOLVED] Filter function
    By Brian Brandt in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-26-2006, 07:59 AM
  7. advanced filter a range:Advanced Filter function
    By Il Principe in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1