Nested double vlookup and match function?

[donny_benjamin]

Hi Everyone,

It's my first time posting and any help with my dilemma will be much appreciated!
I'm working on a new sales report for management. Since it will be updated on a regular basis, I am trying to automate it as much as possible.

My raw data is sorted by month then category instead of category and month (Figure 1, C1 : N2). I created another worksheet to group the categories by month. The challenge has been to find a formula that will retrieve the figures from the raw data tab into the new worksheet. I've tried vlookup, double vlookup, sumifs, and sumproduct functions. I also tried using a helper by combining the month and categories together but to no avail so I created a pivot table instead.

Now I need to create a summary sheet that will retrieve the data by based on month, category (Act, Fcst, etc.), Region and Product and then add a YTD total as show in Figure 3.

Thank you in advance for your help!


Figure 1

Figure 2

Figure 3

 

[KRice]

I noted a few things. In the MTD formula, the last term in the SUMPRODUCT...you want that to cover only the data range from $C$4:$AX$39 rather than $A$4:$AX$39. Note that the other "horizontal" arrays that are constructed also run from C:AX...so you want the data table columns coordinated with them. That got the MTD formulas working.

In the YTD formula, a " 1" is appended to the abbreviated month name in two places to create something that Excel will recognize as a date, so we want something like Jan 1. The space between Jan and 1 is critical, so the formula includes &" 1" (there's a space before the 1)....your version didn't have that. You've specified a large number of columns C:AX, and row 2 holds the month abbreviations. Not all of the cells contain month abbreviations, so DATEVALUE doesn't like that and MONTH doesn't either, so I wrapped the entire construct in an IFERROR and returned a 0 if an error is generated because of the no-data issue. This seems to work and yields an array that looks as it should {1,1,1,1,2,2,2,2,3,3,3,3,0....all 0's}. That final bit seemed to do the trick for the YTD formulas. I haven't finished looking into this---I suspect there is a better way to make the date comparison, but this seems to work.

You may want to make the edits directly in your version after studying the changes I described. Here's the working version:

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2	3/1/2020
3	VLOOKUP(A2,Mapping!$B$24:$C$35,2)
4	Mar			Actual Units	Proj2 Units	Budget Units	PY Units								Actual Units	Proj2 Units	Budget Units	PY Units
5	Sys A			#N/A											#N/A
6	(Units)			Act	Proj	Budget	PY								Act	Proj	Budget	PY
7	US			14	18	2	14								34	42	26	44
8	Europe			0	0	0	0								0	0	0	0
9	Canada			14	11	1	4								25	34	17	26
10	Japan			0	0	0	0								0	0	0	0
11	Latin America			16	6	6	13								37	14	26	23
12	EMA			18	16	13	13								29	34	31	37
13	Greater China			18	9	6	6								53	25	34	41
14	CSA			10	9	8	9								22	48	31	30
15	Total			90	69	36	59								200	197	165	201
16
17	Product 1			#N/A											#N/A
18	(Units)			Act	Proj	Budget	PY								Act	Proj	Budget	PY
19	US			14	12	16	2								19	26	51	15
20	Europe			0	0	0	0								0	0	0	0
21	Canada			2	3	19	7								28	20	24	37
22	Japan			0	0	0	0								0	0	0	0
23	Latin America			18	1	7	12								51	8	21	41
24	EMA			8	19	16	5								29	36	36	29
25	Greater China			3	7	2	11								9	28	15	40
26	CSA			11	11	18	4								21	38	41	31
27	Total			56	53	78	41								157	156	188	193
Template

Cell Formulas
Range	Formula
D5	D5	="MTD "&VLOOKUP($A$2,Mapping!$B$24:$C$35,2,0)
O5	O5	="YTD "&VLOOKUP($A$2,Mapping!$B$24:$C$35,2,0)
D7:G14	D7	=SUMPRODUCT((Data!$B$4:$B$39=Template!$A$5)*(Data!$A$4:$A$39=Template!$A7)*(Data!$C$2:$AX$2=Template!$A$4)*(Data!$C$3:$AX$3=Template!D$4)*(Data!$C$4:$AX$39))
O27:R27,D27:G27,O15:R15,D15:G15	D15	=SUM(D7:D14)
O7:R14	O7	=SUMPRODUCT((Data!$B$4:$B$39=Template!$A$5)*(Data!$A$4:$A$39=Template!$A7)*(IFERROR(MONTH(DATEVALUE(Data!$C$2:$AX$2 &" 1")),0)<=MONTH(DATEVALUE(Template!$A$4&" 1")))*(Data!$C$3:$AX$3=Template!O$4)*(Data!$C$4:$AX$39))
D17,O17	D17	=D$5
D19:G26	D19	=SUMPRODUCT((Data!$B$4:$B$39=Template!$A$17)*(Data!$A$4:$A$39=Template!$A19)*(Data!$C$2:$AX$2=Template!$A$4)*(Data!$C$3:$AX$3=Template!D$4)*(Data!$C$4:$AX$39))
O19:R26	O19	=SUMPRODUCT((Data!$B$4:$B$39=Template!$A$17)*(Data!$A$4:$A$39=Template!$A19)*(IFERROR(MONTH(DATEVALUE(Data!$C$2:$AX$2 &" 1")),0)<=MONTH(DATEVALUE(Template!$A$4&" 1")))*(Data!$C$3:$AX$3=Template!O$4)*(Data!$C$4:$AX$39))

 

[donny_benjamin]

Hi Kirk,
I've tried all the formulas but it still does not compute correctly. After three weeks of this, I'm giving up and will just do it manually for now. Thanks again for all of your help and sorry for the trouble.

Regards,
Don

 

[KRice]

Have you tried clicking on the clipboard icon in my last post...it's located in the upper left corner at the intersection of row and column labels. You can click that and then paste into a new worksheet in your workbook. You may want to temporarily rename your current "Template" worksheet to "template_original". When you paste, be sure to go to cell A1 on the new sheet and then paste. The formulas should access the 'Data' sheet in that workbook and report back the results.

 

[donny_benjamin]

Same results. The formulas don't compute correctly. I'm sure I'm doing something wrong somewhere along the line but don't know what. Or maybe it simply cannot be done in.

 

[KRice]

That's puzzling. Here is the entire file that I assembled from your post above where you included the 'Data' and the 'Template' worksheets. Other than resolving the formula issues I described earlier, I hard wired in a date in cell A2 since I didn't have the 'Mapping' sheet (that apparently has some date references on it).

donny.xlsx

Shared with Dropbox

www.dropbox.com

I'm curious if this looks okay when you open it.