I'm trying to find the formula for column "C" in order to obtain this:
| A | B | C | |
|---|---|---|---|
| 1 | blue | flower | blue |
| 2 | water | blue | |
| 3 | sky | blue | |
| 4 | green | grass | green |
| 5 | frog | green | |
| 6 | yellow | lemon | yellow |
| 7 | sun | yellow |
I've tried with INDEX and MATCH but I haven't found yet the way to go.
Bonus: a unique formula with ARRAYFORMULA would be very nice for my use case.
Given the exact layout and ranges shown in your post, delete everything from Col C and place this array formula in C1:
=ArrayFormula(IF(B2:B="",,VLOOKUP(ROW(A:A),FILTER({ROW(A:A),A:A},A:A<>""),2,TRUE)))
This will lookup each row number for rows where Col B is not empty within a virtual array of only those row numbers where Col A is not empty paired with the value of Col A, and return the result from the second column of that array. Because the final parameter of VLOOKUP is TRUE, any exact row number searched and not present in the limited virtual array will "fall backward" to the last row number that was occupied.
I guess you are looking for this
=ArrayFormula(if(row(A1:A) <= max(if(not(isblank(B1:B)), row(A1:A))),vlookup(row(A1:A),filter({row(A1:A),A1:A},len(A1:A)),2),))
Another way
=ArrayFormula(if(B2:B="",,lookup(row(A2:A),row(A2:A)/if(A2:A<>"",1,0),A2:A)))