# Making col_index_num section of VLOOKUP dynamic.

#### CrackerJack\$

##### New Member
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

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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.

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...

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.

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

Woohoo!!!

It works. I ended up using the first suggestion.

I am humbled...

Many, many thanks to all.

-David

Replies
1
Views
293
Replies
9
Views
147
Replies
3
Views
251
Replies
3
Views
232
Replies
0
Views
355

Threads
1,203,607
Messages
6,056,284
Members
444,855
Latest member
archadiel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back