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.

	A	B	C	D	E	F
1	Date	Description	Numbers during this month		Total numbers for the month	Numbers
2	01/01/2018	X	= ......		Jan-2018	40,000
3	06/13/2018	X			Feb-2018	20,000
4	08/11/2018	X			March-2018	55,000
5	06/16/2018	X			April-2018	33,000

<tbody>
</tbody>

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 ...