Show reference value in table based on date-year

[crackers707]

Hi there,

I have been trying to find a solution everywhere, but have had no luck thus far, so hoping someone here can help me out!

I have two tables that sit alongside one another. One table contains the data with a date where an event occurred. This data is 5000 rows worth. The second table shows a reference value for each month-year and shows numbers for two years (24 rows worth). What I need to be able to do is put a formula in C2 and below that looks in A2 and matches the month-year in E2-E26 and then shows the number in the respective F2-F26 when the month and year matches.

Can this be done? A mockup of my situation below.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]Description[/TD]
[TD]Numbers during this month[/TD]
[TD][/TD]
[TD]Total numbers for the month[/TD]
[TD]Numbers[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]01/01/2018[/TD]
[TD]X[/TD]
[TD]= ......[/TD]
[TD][/TD]
[TD]Jan-2018[/TD]
[TD]40,000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]06/13/2018[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]Feb-2018[/TD]
[TD]20,000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]08/11/2018[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]March-2018[/TD]
[TD]55,000[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]06/16/2018[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]April-2018[/TD]
[TD]33,000[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be amazing!

 

[James006]

Hi,

You could test following formua in cell C2 :

=INDEX($F$2:$F$5,MATCH(EOMONTH(A2,-1)+1,$E$2:$E$5,0))

Hope this will help

 

[crackers707]

Hi,

You could test following formua in cell C2 :

=INDEX($F$2:$F$5,MATCH(EOMONTH(A2,-1)+1,$E$2:$E$5,0))

Hope this will help

Thanks for the help. Unfortunately, it only works where the dates in column A&E are the same. Just need them to match on month and year only. ANy other ideas?

 

[James006]

Hello again,

Sorry ... but have you tried the formula ... ???

The EoMonth bit is added in order to make sure the date in Column A is adjusted to the first of the month ...which seems to be in line with what you have in Column E ...

 

[crackers707]

Hello again,

Sorry ... but have you tried the formula ... ???

The EoMonth bit is added in order to make sure the date in Column A is adjusted to the first of the month ...which seems to be in line with what you have in Column E ...

Ah thanks, my reference list was wrong, but with some fine tuning I think this has worked. Thanks so much!!

 

[James006]

Glad to hear you could fix your problem ...