Concatenate merged cells with Google Sheets

Question

I have this situation on Google Sheets:

I want to concatenate (=A2&B2) with a merged cell, but only the first cell has a value. I want to get the values of the column "Expected results". How I can detect the first value of each work office in this example?

Answer 1

You can use INDEX/AGGREGATE:

=INDEX($A$1:$A$9,AGGREGATE(14,4,(ISBLANK($A$1:$A$9)=FALSE)*(ROW($A$1:$A$9)<=ROW())*ROW($A$1:$A$9),1)) & B2

Edit for google sheets:

=INDEX($A$1:$A$9,LARGE((ISBLANK($A$1:$A$9)=FALSE)*(ROW($A$1:$A$9)<=ROW())*ROW($A$1:$A$9),1)) & B2

Answer 2

When merging a cell, the content appears only as if it were if the first cell of that block. Hence, you need to only use that first value as your reference. For this you need to block the reference, and it would look like this:

=($A$1&B2)

You can check more information about that in this link.

Answer 3

Try this in D2:

=ARRAYFORMULA(IF(B2:B="",,VLOOKUP(ROW(A2:A),FILTER({ROW(A2:A),A2:A},A2:A<>""),2)&B2:B))