I have two columns:

One merged ( to simplify groupings )
One NON-merged with cells next to these merged cells corresponding to the data contained in them. The layout expressed below

Group 1 - Q1
Q2
Group 2 - Q5
Q4
Q3
Group 6 - Q3
Q1
Q2
Group 2 - Q1
Q5

I have a formula column in another spreadsheet to gather question data for each respective group using IF functions. So IF(A:A = "Group 2",[my function],0).

The issue here is that although my formula column works, it will only bring about the first value to the right of the merged cells and none of the others. Excel only sees the merged cell as one cell with a value with the rest being empty.

Is there anyway to make it so that the merged cell is seen as having the same value through all the cells that make it up?