Adding "fixed" data to variable length data

[ben_hur]

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

 

[Roger Govier]

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

 

[ben_hur]

Hi,

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

Thanks for the help, but that just results in a #N/A error - I'm not
sure I understand how the Column() function can help.

Regards,
BH

 

[Roger Govier]

Hi


I do apologise, my formula was missing a closing bracket
Column() returns a column Number.
B2 contains a Number, Row 1 of your table contains Numbers.
Therefore, I assumed that your lookup values in column G of both tables
were numbers.
Basically, you are wanting this table to collect data from either the
actual or projections tables for the equivalent column numbers,
dependent upon whether each individual's data has reached a given month
(noted in column B).

Making it COLUMN(A:A) will return a value of 1, whichever cell it is
entered into. As you drag it across, so it will step up to (B:B) or ,
and so on.
You also need to make your lookup ranges absolute, so my suggestion
would be as follows (it works for me, provided I have data within the
range).
The formula uses COLUMN() throughout, the If statement merely saying
which lookup table to use.

=IF($B2>=COLUMN(A:A),VLOOKUP(COLUMN(A:A),actuals!$G$73:$AD$96,3,FALSE),
VLOOKUP(COLUMN(A:A),projections!$G$73:$AD$96,3,FALSE)