Find the value based on date

[pyclen]

Hi All,

I have the below table from the Bureau of Labor Statistics (Bureau of Labor Statistics Data) showing the pricing index for a given month.

To calculate the increase from one month to another (same or different year) I am using this table with the change being calculated using

=ROUND(((D8-D7)/D7)*100,1)

	Date	Value
Initial Index Value	May-20	179.9 (this is D7 in above formula)
Current Index Value	Mar-22	281.396 (this is D8 in above formula)
% Change	56.4

As of now I am changing the months manually as well as finding the corresponding value for the month for this calculation.
How can I get it to work

1. more automatically, i.e., I select the corresponding months and XL will populate the corresponding values in the last column
2. be dynamic, as every month new data gets published (I have the above table linked to the web source, so once a month I refresh the data for this table)

Any help is highly appreciated

 

[SanjayGMusafir]

What you need can be done dynamically using SUMPRODUCT or INDEX-MATCH Formula.

The only hindrance here is that you haven't uploaded sample data using XL2BB to help you populate formula.

 

[pyclen]

Hi Sanjay,

thank you for your reply however there is no other data to provide and I don;t think it is necessary to use the xl2bb tool for this.
I literally just want to pick a month in both cells (like May2020 and Mar2022) and XL finds the corresponding values in the table that match these months (here 179.9 & 281.396)
The %change is then calculated using the shown formula.

 

[SanjayGMusafir]

You may be right in your own way, but to populate and test formula need to have some data in hand. Anyone who is willing to help you have to go through tedious process to recreate the whole data. You can save them the effort just by posting data through XL2BB.

Hope this clarifies the need.

 

[pyclen]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2
3	Year	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec				Date	Value
4	2012	169.4	169.4	169.4	169	169.7	169.5	172.7	173.7	173.4	173.9	173.7	173.8			Initial Index value	Dec-18	263.2
5	2013	179.9	181	181.5	181.6	182.5	183.4	185.9	185.7	181.7	182.1	182.1	184			Current Index Value	Dec-19	268.1
6	2014	192.8	193.2	192.8	192.3	193.2	195.4	197.3	197.7	197.6	199.2	199.2	201.6			% Change		1.9
7	2015	207.8	207.9	208	207.7	208.5	216.0	216.1	216.1	216.2	216.2	216.1	217.2
8	2016	227.8	228.6	228.6	228.9	229.0	232.0	232.5	233.5	233.7	234.4	234.4	234.7
9	2017	243.7	243.7	243.7	243.7	244.7	247.1	247.4	247.5	245.5	250.3	250.2	250.5
10	2018	257.8	257.8	257.8	257.5	257.2	257.9	260.3	259.1	259.4	260.5	262.3	263.2
11	2019	267.9	267.9	268.6	269	268.5	268.2	268.3	268.4	268.4	268.5	268.4	268.1
12	2020	273.3	274.3	274.6	274.6	274.5	274.5	274.8	274.6	272.8	272.8	272.8	272.9
13	2021	277	277.4	278.1	277.6	277.6	277.6	276.178	276.244	276.365	276.538	276.583	276.518
14	2022	280.03	281.186	281.396	281.303	281.327(P)	281.335(P)	282.669(P)	282.839(P)
Table 1

Cell Formulas
Range	Formula
R4:R5	R4	=M10
R6	R6	=ROUND(((R5-R4)/R4)*100,1)

 

[SanjayGMusafir]

Check this - See R4, R5, also change in B3:M3

All Records.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3	Year	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec				Date	Value
4	2012	169.4	169.4	169.4	169	169.7	169.5	172.7	173.7	173.4	173.9	173.7	173.8			Initial Index value	43,435	263.20
5	2013	179.9	181	181.5	181.6	182.5	183.4	185.9	185.7	181.7	182.1	182.1	184			Current Index Value	43,800	268.10
6	2014	192.8	193.2	192.8	192.3	193.2	195.4	197.3	197.7	197.6	199.2	199.2	201.6			% Change		1.90
7	2015	207.8	207.9	208	207.7	208.5	216	216.1	216.1	216.2	216.2	216.1	217.2
8	2016	227.8	228.6	228.6	228.9	229	232	232.5	233.5	233.7	234.4	234.4	234.7
9	2017	243.7	243.7	243.7	243.7	244.7	247.1	247.4	247.5	245.5	250.3	250.2	250.5
10	2018	257.8	257.8	257.8	257.5	257.2	257.9	260.3	259.1	259.4	260.5	262.3	263.2
11	2019	267.9	267.9	268.6	269	268.5	268.2	268.3	268.4	268.4	268.5	268.4	268.1
12	2020	273.3	274.3	274.6	274.6	274.5	274.5	274.8	274.6	272.8	272.8	272.8	272.9
13	2021	277	277.4	278.1	277.6	277.6	277.6	276.178	276.244	276.365	276.538	276.583	276.518
14	2022	280.03	281.186	281.396	281.303	281.327(P)	281.335(P)	282.669(P)	282.839(P)
Sheet4

Cell Formulas
Range	Formula
R4:R5	R4	=INDEX($A$3:$M$14,MATCH(YEAR(Q4),$A$3:$A$14,0),MATCH(MONTH(Q4),MONTH($A$3:$M$3),0))
R6	R6	=ROUND(((R5-R4)/R4)*100,1)

 

[pyclen]

Thanks Sanjay for the help, and I see that it works for your version of Office

Since I do need this for work, and I am stuck w/XL2019, not 365, I am getting a #value error. I am not sure what is the problem as XL2019 seems to know month/year formula and should know index and match.
I tried modifying the date to numbers, changing the date (I had mmm-yyyy specified, now it is mmm yyyy), the error comes at the last match (bold) =INDEX($A$3:$M$14,MATCH(YEAR(Q4),$A$3:$A$14,0),MATCH(MONTH(Q4),MONTH($A$3:$M$3),0))

Did you change the data type to something, I tried date, text, even made a completely new row for month below the table and adjusted the reference, still #value, not sure what is wrong

 

[SanjayGMusafir]

#value erro

Value error could be because you didn't change the Month Values from B3:M3

Just put 1 Jan, 1 Feb dates in those cells and Format them as "MMM"

Excel Version is not a challenge at all

Please check and update.

 

[pyclen]

i DID

 

[pyclen]

I have everything formatted as you said, same result
maybe attach the XL file you have so I can compare what is wrong w/my file or where I fail to format correctly

B3:m3 all is formatted as MMM