Dynamic Col_Index_Num for Sum Vlookup Array

[AndyPandy27]

Hi All,

I am trying to create a dynamic col_index_num for a sum vlookup array I am using, but without much luck.

I was originally using the formula:

{=SUM(VLOOKUP($B5,'Total Sales (Endo)'!$B$10:$AB$17,[B][COLOR=#ff0000]{3,5,7,9,11,13}[/COLOR][/B],0))}

Which works perfectly. However, I would like the {3,5,7,9,11,13} to be dynamic, rather than static.

In an effort to make this section dynamic, I altered the formula to:

{=SUM(VLOOKUP($B5,'Total Sales (Endo)'!$B$10:$AB$17,[B][COLOR=#ff0000]VLOOKUP(F2,Data!B70:C81,2,0)[/COLOR][/B],0))}

Where Data!B70:C81 was:

Col B | Col C
P1 | 3
P1-P2 | {3,5}
P1-P3 | {3,5,7}
P1-P4 | {3,5,7,9}
etc... down to
P1-P12 | {3,5,7,9,11,13,15,17,19,21,23,25}

And F2 = "P1-P6" (but could be any one of the 12 values in Column B as above).

However this returned a #VALUE! error.

I then thought that perhaps I needed to change the format of the details in Col C, so amended that to:
Col B | Col C
P1 | 3
P1-P2 | "{3,5}"
P1-P3 | "{3,5,7}"
P1-P4 | "{3,5,7,9}"

But again, this returned a #VALUE! error.

Searching around again, I found this forum post (http://www.mrexcel.com/forum/showthread.php?222936-Making-col_index_num-section-of-VLOOKUP-dynamic), but couldn't quite make this work for me, as my situation is not consecutive columns.

Does anyone know of a way that I could make my formula completely dynamic?

Thanks,

AP

 

[RoryA]

Create a defined name called ListCols with a refersto like this:
=EVALUATE(VLOOKUP($F$2,$B$70:$C$81,2,FALSE))

then use:
{=SUM(VLOOKUP($B5,'Total Sales (Endo)'!$B$10:$AB$17,LIstCols,0))}

 

[AndyPandy27]

RoryA - you Sir, are a genius.

Worked like a charm first time - thank you VERY much indeed.

Fantastic job - this is excellent.