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!
Bookmarks