Making col_index_num section of VLOOKUP dynamic.

[CrackerJack$]

Hi all,

After almost a week of searching the net and almost, but not quite, finding the answer, I've decided to bring my query to this board!!!

I am responsible for the financial reporting of multiple entities where I work. I have created ONE Excel spreadsheet where each entity has its own tab that contains the detailed numbers and roll-ups.

On the analysis tabs, I want to let the user be able to select, via roll-down menus the entities, month-end, period under review and comparable to use. I have that part covered.

I want to use ONE formula, preferably VLOOKUP, to do this. Why one formula: good hygiene is all!

The data table for an entity consists of months presented in columns.

If a user selects a quarterlypresentation, the formula would be:

{=SUM(VLOOKUP(D15;F14:S17;{2;3;4})} where {2;3;4} returns the sum of columns 2,3 and 4 in the table. Notice the brackets before and at the end of the formula (not quite sure why...)

PROBLEM:
I would like to link to a cell that contains either {2} or {2;3;4} or {2;3;4;5;6;7;8;9;10;11;12;13} representing respectively a month, a quarter or a year and make that portion of the VLOOKUP formula dynamic.

So far, the only way I have found is to make the formula fixed and multiple by either 1 or 0 to activate or desactivate that sub-portion of the formula.

=SUM(VLOOKUP(D15;F14:S17;{2;3;4}))*E5+SUM(VLOOKUP(D15;F14:S17;{2;3;4}))*E6+SUM(VLOOKUP(D15;F14:S17;{2;3;4;5;6;7;8;9;10;11;12;13}))*E7

It seems too heavy to me...

Would anyone have a better way?

ThX

 

[hatman]

Welcome to the board!

The curly brackets at surrounding the formula are placed there by Excel when you enter an array formula using Ctrl+Shift+Enter, which is necessary if your formula will iterate over multiple values, as yours is.

What about this modification:

=SUM(VLOOKUP(D15,F14:S17,row(indirect("2:"&a1)))) confirmed with Ctrl+Shift+Enter. In this scenario a1 will contain either 2, 4 or 13, either by drop-down or calculation. if by calculation, you could place the calculation in the formula to replace A1.

 

[BJungheim]

One way you can do it is by Replacing the column reference with a Choose Statement: CHOOSE(E5;SMonth;SQuarter;SYear)

Then you just create 3 named ranges:
SMonth
=2

SQuarter
={2;3;4}

SYear
={2;3;4;5;6;7;8;9;10;11;12;13}

If E5 = Month it will return SMonth as the column lookup, if E5 = Quarter it will return SQuarter, etc...

 

[Andrew Poulsom]

For a non-array formula approach:

=SUM(INDEX(G14:G17,MATCH(D15,F14:F17)):INDEX(F14:S17,MATCH(D15,F14:F17),E5))

where E5 contains 2, 4 or 13.

 

[barry houdini]

Using a similar approach

=SUM(VLOOKUP(D15;F14:S17;ROW(INDIRECT("2:"&LOOKUP(E5;{"m";"q";"y"};{2;4;13})))))

confirmed with CTRL+SHIFT+ENTER

E5 to contain m, q, or y

or slightly different....

=SUM(OFFSET(G13;MATCH(D15;F14:F17);;1;LOOKUP(E5;{"m";"q";"y"};{1,3,12})))

only requires ENTER

or pinching from Andrew ( ) you can shorten that last to

=SUM(OFFSET(G13;MATCH(D15;F14:F17);;;E5))

where E5 contains 1, 3 or 12

 

[CrackerJack$]

Woohoo!!!

It works. I ended up using the first suggestion.

I am humbled...

Many, many thanks to all.

-David