|    A   |    B   |    C   |    D   |    E   |    F    |     
--|--------|--------|--------|--------|--------|---------|-------
1 |    1   |        |  2021  |  2022  |  2021  |   2022  |
2 |    1   |        |  test  |    1   |  test  |         |
3 |    1   |        |    2   |    2   |    2   |         |
4 |    2   |        |    3   |    3   |    3   |         |
5 |    3   |        |        |        |        |         |
6 |    3   |        |        |        |        |         |
7 |    1   |        |        |        |        |         |

In Columns C:E I want to insert a unique list of the values in Column A.

Additionally, if

1. the value in Row 1 is smaller than the value in Cell $F$1 AND
   
2. one of the UNIQUE values equals the number 1
   

then the word "test" should be displayed instead of the number 1.

I tried to go with this formula:

C2:E2 = =IF(AND(C$1<$F$1,UNIQUE($A$1:$A$7)=1),"test",UNIQUE($A$1:$A$7))

However, somehow this formula ignores the conditions and keeps displaying a 1 instead of the word test.

What do I need to change to make it work?

CodePudding user response：

You need to use the multiplication sign (*) instead of AND in array formulas:

=IF((C$1<$F$1)*(UNIQUE($A$1:$A$7)=1),"test",UNIQUE($A$1:$A$7))