A formula inside Curly braces inside Vlookup does not work, but seems good in theory

[fnjww6]

Thank you for reading.

I started wanting to set up a report to in excel to go find data for a month period (single cell), quarter to date period (multiple cells) and year to date period (multiple cells).

Using curly braces in the VLOOKUP helped = sumproduct(vlookup(a1,'sheet1'!a1:z200, {1,2,3} ,0) .
This works great when you have a certain set scenario {1,2,3} . You can also have a limited number of scenarios and use if statements for the next scenarios (this worked for 12 months).

I am trying to lookup more columns now (over 60) based on a date and period of inquiry (month, quarter, year). I cannot use over 60 if, then statements.

I tried to add a cell reference or vlookup formula in the curly braces above, but neither worked. I am guessing they were not read or they did not return the correct information for the curly braces.

ex: if "Quarter to Date", the sumproduct'
vlookup the product name , on the worksheet tab (combined name from a cell reference),
vlookup the value of the {data } , by what the date of the period was (x5) from the control worksheet list,
this would in theory return what cell the sum based on the parameters and give the { data }.

The vlookup will return the correct group:
1
1,2,3
1,2,3,4,5,6
etc.

But, the data returned by the vlookup was not recognized by the curly braces { data}.
This resulted in a reference error.


=IF(X$4="Quarter to Date",sumproduct(vlookup(B8,indirect("'#,"&X$1&",#'!"&"$A:$CC"),{(VLOOKUP(X$5,control!$V$132:$Z$211,3,0))},0)),"test")

I tried other ways:

'=IF(X$4="Month to Date",sumproduct(vlookup(B8,indirect("'#,"&X$1&",#'!"&"$A:$CC"),{X6},0)),"test")

=SUMPRODUCT(VLOOKUP(B8,INDIRECT("'#,"&X$1&",#'!"&"$A:$CC"),{INDEX(control!$V$133:$AA$204,MATCH(X5,control!$V$133:$V$204,0),4)},0))

{concatenate(...)} - concatenate the data to make sure it was text



Basically, If I could lookup inside the { } , then I could return the columns I wanted to sum together. these columns are even laid out in a table on the control tab as guide. If I could have this VLOOKUP in the { curly braces}, it would work.
In theory..


I appreciate your help.

I don't have the images hosted to link a URL , but I can email them.

Thank you,
Jeremy