Look up values of month from previous year

[Forestq]

Hi,

I need to write a function, which find value from month from previous year:

Below is example of my data (I have year starting from 1992 - but it should not have meaning)

Year	Month	Quater	RepQua	ID	Value	PrevValue
2015	Jan	Q1	FY15Q1	A1B	10	-
2015	Apr	Q2	FY15Q2	B2C	20%	-
2015	Apr	Q2	FY15Q2	C11A	3%	-
2015	May	Q3	FY15Q3	H76	4	-
2015	June	Q3	FY15Q3	J87Q	62	-
2016	Jan	Q1	FY16Q1	A1B	20	should be 10
2016	Apr	Q2	FY15Q2	B2C	5%	should be 20%
2016	Apr	Q2	FY15Q2	C11A	7	should be 3%
2016	May	Q3	FY15Q3	H76	54	should be 4
2016	June	Q3	FY15Q3	J87Q	70	should be 62

<tbody>
</tbody>

Thank you in advance.

 

[AlanY]

are the values 10, 20% etc are all percentages or mixed with numbers?

 

[DiscoPistol]

Hi,

If you assume that the Names ranges are as suggested this seems to work for me if you enter as an array (Control/Shift/Enter)

=SUM((Year=C11-1)*(Month=D11)*(ID = G11)*(Value))

 

[AlanY]

try this

Excel 2012
	A	B	C	D	E	F	G
1	Year	Month	Quater	RepQua	ID	Value	PrevValue
2	2015	Jan	Q1	FY15Q1	A1B	10	-
3	2015	Apr	Q2	FY15Q2	B2C	20%	-
4	2015	Apr	Q2	FY15Q2	C11A	3%	-
5	2015	May	Q3	FY15Q3	H76	4	-
6	2015	June	Q3	FY15Q3	J87Q	62	-
7	2016	Jan	Q1	FY16Q1	A1B	20	10
8	2016	Apr	Q2	FY15Q2	B2C	5%	0.2
9	2016	Apr	Q2	FY15Q2	C11A	7	0.03
10	2016	May	Q3	FY15Q3	H76	54	4
11	2016	June	Q3	FY15Q3	J87Q	70	62
Sheet2

Cell Formulas
Range	Formula
G7	=SUMPRODUCT(--($A$2:$A$11=(A7-1)),--($B$2:$B$11=B7),--($C$2:$C$11=C7),--($E$2:$E$11=E7),$F$2:$F$11)

and conditional format column with cell value <1 with percentage

Excel 2012
	A	B	C	D	E	F	G
1	Year	Month	Quater	RepQua	ID	Value	PrevValue
2	2015	Jan	Q1	FY15Q1	A1B	10	-
3	2015	Apr	Q2	FY15Q2	B2C	20%	-
4	2015	Apr	Q2	FY15Q2	C11A	3%	-
5	2015	May	Q3	FY15Q3	H76	4	-
6	2015	June	Q3	FY15Q3	J87Q	62	-
7	2016	Jan	Q1	FY16Q1	A1B	20	10
8	2016	Apr	Q2	FY15Q2	B2C	5%	20%
9	2016	Apr	Q2	FY15Q2	C11A	7	3%
10	2016	May	Q3	FY15Q3	H76	54	4
11	2016	June	Q3	FY15Q3	J87Q	70	62
Sheet2

 

[Forestq]

thanks AlanY!

it's possible to modify:

- Type -> N it's number and column "Value N" has only number
- Type -> P it's percentage and column "Value P" has only percetage

...id	Type	Value N	Value P	PrevValue
A1B	N	12		looking value 10
B2C	P		23%	looking value 20%
C11A	P		5%	looking value 3%
H75	N	7		looking value 4
8UP	N	14		looking value 105

<tbody>
</tbody>

 

[AlanY]

this LONG formula that I'm sure someone can improve it should work without to seperate it into 2 columns and CF;

Excel 2012
	A	B	C	D	E	F	G
1	Year	Month	Quater	RepQua	ID	Value
2	2015	Jan	Q1	FY15Q1	A1B	10
3	2015	Apr	Q2	FY15Q2	B2C	20%
4	2015	Apr	Q2	FY15Q2	C11A	3%
5	2015	May	Q3	FY15Q3	H76	4
6	2015	June	Q3	FY15Q3	J87Q	62
7	2016	Jan	Q1	FY16Q1	A1B	20	10
8	2016	Apr	Q2	FY15Q2	B2C	5%	20%
9	2016	Apr	Q2	FY15Q2	C11A	7	3%
10	2016	May	Q3	FY15Q3	H76	54	4
11	2016	June	Q3	FY15Q3	J87Q	70	62
Sheet2

Cell Formulas
Range	Formula
G7	=IF(SUMPRODUCT(--($A$2:$A$11=(A7-1)),--($B$2:$B$11=B7),--($C$2:$C$11=C7),--($E$2:$E$11=E7),$F$2:$F$11)<1,TEXT(SUMPRODUCT(--($A$2:$A$11=(A7-1)),--($B$2:$B$11=B7),--($C$2:$C$11=C7),--($E$2:$E$11=E7),$F$2:$F$11),0&"%"),SUMPRODUCT(--($A$2:$A$11=(A7-1)),--($B$2:$B$11=B7),--($C$2:$C$11=C7),--($E$2:$E$11=E7),$F$2:$F$11))

 

[AlanY]

which is the same as an extra column but more readable

Excel 2012
	A	B	C	D	E	F	G	H
1	Year	Month	Quater	RepQua	ID	Value
2	2015	Jan	Q1	FY15Q1	A1B	10
3	2015	Apr	Q2	FY15Q2	B2C	20%
4	2015	Apr	Q2	FY15Q2	C11A	3%
5	2015	May	Q3	FY15Q3	H76	4
6	2015	June	Q3	FY15Q3	J87Q	62
7	2016	Jan	Q1	FY16Q1	A1B	20	10	10
8	2016	Apr	Q2	FY15Q2	B2C	5%	0.2	20%
9	2016	Apr	Q2	FY15Q2	C11A	7	0.03	3%
10	2016	May	Q3	FY15Q3	H76	54	4	4
11	2016	June	Q3	FY15Q3	J87Q	70	62	62
Sheet2

Cell Formulas
Range	Formula
G7	=SUMPRODUCT(--($A$2:$A$11=(A7-1)),--($B$2:$B$11=B7),--($C$2:$C$11=C7),--($E$2:$E$11=E7),$F$2:$F$11)
H7	=IF(G7<1,TEXT(G7,0&"%"),G7)

 

[Forestq]

I did exaclty the same way.

Thanks AlanY for quick help!

 

[AlanY]

you're welcome

 

[Forestq]

also,

we can do like below:

=if(F2="N",
IF(SUMPRODUCT(--($A$2:$A$16=(A2-1)),--($B$2:$B$16=B2),--($C$2:$C$16=C2),--($E$2:$E$16=E2),$G$2:$G$16)<1,TEXT(SUMPRODUCT(--($A$2:$A$16=(A2-1)),--($B$2:$B$16=B2),--($C$2:$C$16=C2),--($E$2:$E$16=E2),$G$2:$G$16),0&"%"),SUMPRODUCT(--($A$2:$A$16=(A2-1)),--($B$2:$B$16=B2),--($C$2:$C$16=C2),--($E$2:$E$16=E2),$G$2:$G$16)),


IF(SUMPRODUCT(--($A$2:$A$16=(A2-1)),--($B$2:$B$16=B2),--($C$2:$C$16=C2),--($E$2:$E$16=E2),$H$2:$H$16)<1,TEXT(SUMPRODUCT(--($A$2:$A$16=(A2-1)),--($B$2:$B$16=B2),--($C$2:$C$16=C2),--($E$2:$E$16=E2),$H$2:$H$16),0&"%"),SUMPRODUCT(--($A$2:$A$16=(A2-1)),--($B$2:$B$16=B2),--($C$2:$C$16=C2),--($E$2:$E$16=E2),$H$2:$H$16)))

or

=IF(F2="N",SUMPRODUCT(--($A$2:$A$16=(A2-1)),--($B$2:$B$16=B2),--($C$2:$C$16=C2),--($E$2:$E$16=E2),$G$2:$G$16),SUMPRODUCT(--($A$2:$A$16=(A2-1)),--($B$2:$B$16=B2),--($C$2:$C$16=C2),--($E$2:$E$16=E2),$H$2:$H$16))