Hi,

I'm trying to merge together some variable data to some fixed data
within Excel but am struggling to come up with an elegant solution to
it.

I have two worksheets, one containing values (predictions) in a fixed
24 cell by 24 cell area, the other sheet contains rows of data
(actuals) which is of variable length (i.e. some rows are 4 columns
wide, others 10). What I'm trying to make is a worksheet that picks up
the actual data and then where none exists picks up the predictions
data.

In column A of the sheet I am trying to create I have the names of the
data ranges and in column B I have the number of months data. i.e.

A B C D E F
1 Months 1 2 3 4
2 Robert 4 20 25 30
3 David 2 15 10

So in cell C2 I am using the formula..

=IF($B2=C$1,
VLOOKUP($B2,actuals!G73:AD96,3,FALSE),VLOOKUP($B2, projections!G73:AD96,3,FALSE)

This picks up the data from the actuals column while ever the month
number is less or equal to the amount of months and then moves onto the
predictions sheet whenever it is above this figure. However as the
data is of a variable length, what is correct for Row 2 will be wrong
for Row 3 because the col_index_num of the second vlookup is wrong and
I therefore cannot drag down. Is there any way around this??

Thanks in advance.

BH