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]

Give this try to see if it gives the results expected:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Mar	MTD Mar					YTD Mar
2	Sys 1	Act	Fcst	Bud	PY		Act	Fcst	Bud	PY
3	N. America	10	5	4	4		20	15	13	19
4	LATAM	2	3	10	1		14	17	19	13
5	EMEA	5	7	6	5		20	18	11	20
6	APAC	4	9	9	1		11	27	22	11
7
8	Sys 2
9	N. America	6	8	4	7		14	22	16	18
10	LATAM	9	7	1	2		24	13	15	17
11	EMEA	9	1	1	7		15	7	9	22
12	APAC	3	8	9	7		10	23	20	17
Sheet1

Cell Formulas
Range	Formula
B1	B1	="MTD "&A1
G1	G1	="YTD "&A1
B3:E6	B3	=SUMPRODUCT((Data!$A$3:$A$18=$A3)*(Data!$B$3:$B$18=$A$2)*(Data!$C$1:$N$1=$A$1)*(Data!$C$2:$N$2=B$2)*(Data!$C$3:$N$18))
G3:J6	G3	=SUMPRODUCT((Data!$A$3:$A$18=$A3)*(Data!$B$3:$B$18=$A$2)*(Data!$C$2:$N$2=G$2)*(Data!$C$3:$N$18))
B9:E12	B9	=SUMPRODUCT((Data!$A$3:$A$18=$A9)*(Data!$B$3:$B$18=$A$8)*(Data!$C$1:$N$1=$A$1)*(Data!$C$2:$N$2=B$2)*(Data!$C$3:$N$18))
G9:J12	G9	=SUMPRODUCT((Data!$A$3:$A$18=$A9)*(Data!$B$3:$B$18=$A$8)*(Data!$C$2:$N$2=G$2)*(Data!$C$3:$N$18))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			Jan	Jan	Jan	Jan	Feb	Feb	Feb	Feb	Mar	Mar	Mar	Mar
2	Region	Products	Act	Fcst	Bud	PY	Act	Fcst	Bud	PY	Act	Fcst	Bud	PY
3	N. America	Sys 1	6	4	1	9	4	6	8	6	10	5	4	4
4	N. America	Sys 2	7	5	2	5	1	9	10	6	6	8	4	7
5	N. America	Sys 3	2	9	1	9	4	9	2	4	4	5	9	6
6	N. America	Sys 4	10	7	3	5	7	7	2	3	5	9	1	9
7	LATAM	Sys 1	9	9	4	5	3	5	5	7	2	3	10	1
8	LATAM	Sys 2	10	4	10	6	5	2	4	9	9	7	1	2
9	LATAM	Sys 3	1	9	8	2	3	1	3	4	10	5	10	2
10	LATAM	Sys 4	3	6	4	10	6	10	10	5	7	8	7	10
11	EMEA	Sys 1	7	4	4	9	8	7	1	6	5	7	6	5
12	EMEA	Sys 2	2	5	5	9	4	1	3	6	9	1	1	7
13	EMEA	Sys 3	4	3	8	6	10	9	1	9	2	7	7	6
14	EMEA	Sys 4	9	10	7	1	4	10	5	2	8	1	3	2
15	APAC	Sys 1	1	10	4	8	6	8	9	2	4	9	9	1
16	APAC	Sys 2	1	7	3	7	6	8	8	3	3	8	9	7
17	APAC	Sys 3	2	1	4	5	9	4	8	10	8	4	9	3
18	APAC	Sys 4	8	4	2	5	9	8	2	1	1	5	7	7
Data

Cell Formulas
Range	Formula
C3:N18	C3	=RANDBETWEEN(1,10)

 

[donny_benjamin]

Hi Kirk,
I'm working on it now. Trying to make sense of the formulas in the meantime. Thank you so very much for your help and quick response. Will report back with the results soon.

 

[donny_benjamin]

Hi Kirk,
Your formulas for MTD work flawlessly. For YTD, the formula works but when I select a different month, the totals don't change. For example, if I change the month to Feb, I should have 10 units in for System 1 in N. America instead of 20. I tried to add a sumif into the formula but obviously it did not work. Any thoughts? Thank you!

 

[KRice]

I'm looking...sorry about the oversight. I stripped out the month altogether in that formula, but should have tweaked it.

 

[KRice]

Give this a try...a bit more convoluted than I anticipated, but I think it should work:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Feb	MTD Feb					YTD Feb
2	Sys 1	Act	Fcst	Bud	PY		Act	Fcst	Bud	PY
3	N. America	4	6	8	6		10	10	9	15
4	LATAM	3	5	5	7		12	14	9	12
5	EMEA	8	7	1	6		15	11	5	15
6	APAC	6	8	9	2		7	18	13	10
7
8	Sys 2
9	N. America	1	9	10	6		8	14	12	11
10	LATAM	5	2	4	9		15	6	14	15
11	EMEA	4	1	3	6		6	6	8	15
12	APAC	6	8	8	3		7	15	11	10
sumproduct-2way

Cell Formulas
Range	Formula
B1	B1	="MTD "&A1
G1	G1	="YTD "&A1
B3:E6	B3	=SUMPRODUCT((Data!$A$3:$A$18=$A3)*(Data!$B$3:$B$18=$A$2)*(Data!$C$1:$N$1=$A$1)*(Data!$C$2:$N$2=B$2)*(Data!$C$3:$N$18))
G3:J6	G3	=SUMPRODUCT((Data!$A$3:$A$18=$A3)*(Data!$B$3:$B$18=$A$2)*(MONTH(DATEVALUE(Data!$C$1:$N$1&" 1"))<=MONTH(DATEVALUE($A$1&" 1")))*(Data!$C$2:$N$2=G$2)*(Data!$C$3:$N$18))
B9:E12	B9	=SUMPRODUCT((Data!$A$3:$A$18=$A9)*(Data!$B$3:$B$18=$A$8)*(Data!$C$1:$N$1=$A$1)*(Data!$C$2:$N$2=B$2)*(Data!$C$3:$N$18))
G9:J12	G9	=SUMPRODUCT((Data!$A$3:$A$18=$A9)*(Data!$B$3:$B$18=$A$8)*(MONTH(DATEVALUE(Data!$C$1:$N$1&" 1"))<=MONTH(DATEVALUE($A$1&" 1")))*(Data!$C$2:$N$2=G$2)*(Data!$C$3:$N$18))

 

[donny_benjamin]

Hi Kirk,
So sorry for my late response. Thank you so much for the updated formula. For some reason, the YTD formulas like the one in G3, does not calculate. Instead it brings the number for that particular month. I created a simple table like yours and the formulas work flawlessly but I can't get it to work in my spreadsheet. I'm stomped yet again.

 

[KRice]

Interesting...so the B3 formula works:
=SUMPRODUCT((Data!$A$3:$A$18=$A3)*(Data!$B$3:$B$18=$A$2)*(Data!$C$1:$N$1=$A$1)*(Data!$C$2:$N$2=B$2)*(Data!$C$3:$N$18))
and the G3 formula returns the month number?...So if you enter "Mar" in A1, you get 3 somewhere? The G3 formula is:
=SUMPRODUCT((Data!$A$3:$A$18=$A3)*(Data!$B$3:$B$18=$A$2)*(MONTH(DATEVALUE(Data!$C$1:$N$1&" 1"))<=MONTH(DATEVALUE($A$1&" 1")))*(Data!$C$2:$N$2=G$2)*(Data!$C$3:$N$18))

The difference between these formulas is this portion in the G3 formula, which is absent in the B3 formula:
(MONTH(DATEVALUE(Data!$C$1:$N$1&" 1"))<=MONTH(DATEVALUE($A$1&" 1")))*(Data!$C$2:$N$2=G$2)

First let's confirm that you're using the same column headings above the MTD block and the YTD block...in other words, I see Act, Fcst, Bud, and PY. Are these the same between both blocks?
If so, that rules out the B$2 and G$2 terms in the formulas, leaving only this:
(MONTH(DATEVALUE(Data!$C$1:$N$1&" 1"))<=MONTH(DATEVALUE($A$1&" 1")))
I suspect this is the issue. On your "Data" worksheet, what are the column headings?...are you using three letter abbreviations for the months: "Jan", "Feb", etc.

If you click in G3 (upper left corner of the first YTD block, go to the formula bar and select with the mouse the following:
DATEVALUE(Data!$C$1:$N$1&" 1")
then hit F9 and see if an array appears in the formula bar...something looking like this:
{43831,43831,43831,43831,43862,43862,43862,43862,43891,43891,43891,43891}
**** Hit Esc to cancel out of this, otherwise you'll overwrite the formula ****

Then do the same thing with:
DATEVALUE($A$1&" 1")
Do you see something like this: {43831}
**** and hit Esc again ****

Your numbers may be different...this is the date of January 1, 2020...I have "Jan" in cell A1 so this part of the formula converts the month abbreviation to a numeric value so that we can determine whether one date comes before another.

If there are no surprises, copy the first formula that gives an unexpected result and post back with it. You might want to load up the XL2BB add-in (see link in my signature block) to copy a small section of your worksheet for posting. Speaking of that, have you tried copying directly from my post #6? Click on the clipboard icon in the upper left at the intersection of rows and column labels (between the 1 and A) and then paste directly into a blank worksheet in cell A1.

 

[donny_benjamin]

Hi Kirk,
Thank you for your feedback. I've tried everything that you suggested but somewhere along the line I'm making a mistake and I can't figure it out. I've even tried adding pivot tables and helper cells to make the formulas work but with limited success. The biggest challenge is the YTD. Below is a sample as you suggested. Thank you again for all your help.

Sample.xlsx
	A	B	C	D	E	F	G							N	O	P	Q	R
2	March 2020
3
4	Mar			Actual Units	Proj2 Units	Budget Units	PY Units								Actual Units	Proj2 Units	Budget Units	PY Units
5	Sys A			MTD Mar											YTD Mar
6	(Units)			Act	Proj	Budget	PY								Act	Proj	Budget	PY
7	US			#VALUE!	#VALUE!	#VALUE!	#VALUE!								72	70	58	85
8	Europe			#VALUE!	#VALUE!	#VALUE!	#VALUE!								-	-	-	-
9	Canada			#VALUE!	#VALUE!	#VALUE!	#VALUE!								51	60	51	82
10	Japan			#VALUE!	#VALUE!	#VALUE!	#VALUE!								-	-	-	-
11	Latin America			#VALUE!	#VALUE!	#VALUE!	#VALUE!								66	59	61	74
12	EMA			#VALUE!	#VALUE!	#VALUE!	#VALUE!								61	60	50	61
13	Greater China			#VALUE!	#VALUE!	#VALUE!	#VALUE!								85	63	69	72
14	CSA			#VALUE!	#VALUE!	#VALUE!	#VALUE!								45	100	55	51
15	Total			#VALUE!	#VALUE!	#VALUE!	#VALUE!								380	412	344	425
16
17	Product 1			MTD Mar											YTD Mar
18	(Units)			Act	Proj	Budget	PY								Act	Proj	Budget	PY
19	US			#VALUE!	#VALUE!	#VALUE!	#VALUE!								55	58	95	38
20	Europe			#VALUE!	#VALUE!	#VALUE!	#VALUE!								-	-	-	-
21	Canada			#VALUE!	#VALUE!	#VALUE!	#VALUE!								44	50	38	63
22	Japan			#VALUE!	#VALUE!	#VALUE!	#VALUE!								-	-	-	-
23	Latin America			#VALUE!	#VALUE!	#VALUE!	#VALUE!								80	29	55	71
24	EMA			#VALUE!	#VALUE!	#VALUE!	#VALUE!								44	77	81	57
25	Greater China			#VALUE!	#VALUE!	#VALUE!	#VALUE!								31	59	53	75
26	CSA			#VALUE!	#VALUE!	#VALUE!	#VALUE!								43	83	79	55
27	Total			#VALUE!	#VALUE!	#VALUE!	#VALUE!								297	356	401	359
Template

Cell Formulas
Range	Formula
A4	A4	=VLOOKUP(A2,Mapping!$B$24:$C$35,2)
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!$A$4:$AX$39))
D15:G15,O27:R27,D27:G27,O15:R15	D15	=SUM(D7:D14)
O7:R14	O7	=SUMPRODUCT((Data!$B$4:$B$39=Template!$A$5)*(Data!$A$4:$A$39=Template!$A7)*(MONTH(DATEVALUE(Data!$C$2:$AX$2&"1"))<=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!$A$4:$AX$39))
O19:R26	O19	=SUMPRODUCT((Data!$B$4:$B$39=Template!$A$17)*(Data!$A$4:$A$39=Template!$A19)*(MONTH(DATEVALUE(Data!$C$2:$AX$2&"1"))<=MONTH(DATEVALUE(Template!$A$4&"1")))*(Data!$C$3:$AX$3=Template!O$4)*(Data!$C$4:$AX$39))

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=Mapping!$B$23:$B$32

Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2		Month	Jan	Jan	Jan	Jan	Feb	Feb	Feb	Feb	Mar	Mar	Mar	Mar
3	Region	Product Line	Actual Units	Proj2 Units	Budget Units	PY Units	Actual Units	Proj2 Units	Budget Units	PY Units	Actual Units	Proj2 Units	Budget Units	PY Units
4	Canada	Sys A	7	20	4	7	4	3	12	15	14	11	1	4
5	Canada	Sys B	3	3	12	1	9	2	16	4	20	6	2	9
6	Canada	Sys C	16	7	8	2	3	3	12	9	5	14	14	14
7	Canada	Product 1	11	15	3	10	15	2	2	20	2	3	19	7
8	Canada	Product 2	3	12	13	12	17	20	14	14	9	6	11	19
9	Canada	Product 3	6	16	1	5	5	3	18	19	1	6	1	7
10	CSA	Sys A	7	19	8	6	5	20	15	15	10	9	8	9
11	CSA	Sys B	14	6	18	16	8	11	14	1	20	12	5	1
12	CSA	Sys C	6	10	11	15	20	9	6	5	16	7	2	13
13	CSA	Product 1	4	10	8	7	6	17	15	20	11	11	18	4
14	CSA	Product 2	13	3	20	4	8	17	20	20	11	17	5	7
15	CSA	Product 3	10	6	5	20	18	10	6	15	15	16	2	1
16	EMA	Sys A	5	3	12	5	6	15	6	19	18	16	13	13
17	EMA	Sys B	12	5	10	6	2	17	20	16	13	19	2	2
18	EMA	Sys C	19	1	12	10	17	2	12	11	7	20	1	9
19	EMA	Product 1	13	10	12	13	8	7	8	11	8	19	16	5
20	EMA	Product 2	9	5	7	15	19	12	3	10	1	2	12	2
21	EMA	Product 3	13	12	18	18	19	19	6	16	19	8	12	5
22	Greater China	Sys A	20	6	16	16	15	10	12	19	18	9	6	6
23	Greater China	Sys B	4	3	15	18	3	3	6	12	9	8	1	8
24	Greater China	Sys C	1	19	11	3	4	12	3	6	9	14	15	18
25	Greater China	Product 1	2	2	4	16	4	19	9	13	3	7	2	11
26	Greater China	Product 2	9	3	14	8	18	2	15	7	15	7	19	7
27	Greater China	Product 3	2	11	5	16	8	17	2	16	6	20	3	1
28	Latin America	Sys A	10	6	19	8	11	2	1	2	16	6	6	13
29	Latin America	Sys B	6	19	17	8	5	20	7	9	13	16	5	1
30	Latin America	Sys C	17	17	2	11	5	18	6	2	20	9	2	18
31	Latin America	Product 1	20	2	10	18	13	5	4	11	18	1	7	12
32	Latin America	Product 2	20	6	13	6	3	3	17	4	16	16	7	8
33	Latin America	Product 3	17	15	16	20	3	7	18	8	5	16	9	9
34	US	Sys A	7	20	10	10	13	4	14	20	14	18	2	14
35	US	Sys B	14	20	14	14	16	12	1	19	7	2	15	12
36	US	Sys C	20	12	12	11	11	17	6	4	12	8	11	19
37	US	Product 1	1	12	17	2	4	2	18	11	14	12	16	2
38	US	Product 2	4	1	15	12	11	4	13	9	10	16	4	13
39	US	Product 3	11	12	19	11	17	15	6	13	5	2	16	7
Data

 

[KRice]

Do you have a small sample of the Mapping sheet? I'd like to confirm a few entries.