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

fnjww6

New Member
Joined
Feb 20, 2019
Messages
1
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

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

Which adblocker are you using?

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
Back
Top