excelexcel-formula

How can I get a list of boxes at each site

I am trying to make an excel formula that will tell me which site has which box. Basically if the site matches what site we are looking for and the value in the cell is > 0 then return that Box color.

Table

I need the table to look something like this

Outcome

I have tried to use the If statement, however I am not able get the formula to repeat the statement if outcome is false. The formula I am using, =IF(AND($H4=$A4,C4>0),C$1," "). The table I am getting is

enter image description here

I do not want the table to have the blank cells.

Solution

  • Try nested FILTER() function.

    =FILTER($B$1:$F$1,FILTER($B$2:$F$8,$A$2:$A$8=$A13)>0)

    enter image description here