Can I use Concatenate to Apply a value to a certain set of cells in a column?

[Willygubbins]

Hi, I am looking to apply set of values to a set of cels in a column based on criteria being met on row, it is for filtering purposes, so i can then use a normal =IF(AND for a secondary filter to mark one row. I know it can be achieved in pivot tables, but i require the single row formula to pick out rows for suture use. the link below is an example of what i mean. Thanks

https://www.screencast.com/t/r1AhVPoZG