CODEWITHSUNDEEP
arraysexcel
Glenise
Glenise

Reputation: 11

Identifying cells that fall in a given date range in excel

I am making a formula in the column "January" which is supposed to look the range of dates in B2:F2 and return the the contents of whichever cell in that array that falls in the month of January. Hence, if my dates are

1/1/2015    2/2/2015    3/3/2015    4/4/2015    5/5/2015

I would like the corresponding January column to return 1/1/2015.
I've found Index/Match function examples on this site and others, but they all resemble the following:

=INDEX(B2:F2,MATCH(SUMPRODUCT(1*(MONTH(B2:F2)=3)),B2:F2,0))

I know that COUNTIF/S, SUMPRODUCT and others can give me sums of the cells they find, but I don't need the sums. I need to MATCH based on the value given by the month function. Any help would be appreciated as I am tearing my hair out. Many thanks!

Upvotes: 1

Views: 77

Answers (1)

vknowles
vknowles

Reputation: 784

Similar to @tigeravatar, I came up with this:

{=INDEX(B2:F2,MATCH(A2,MONTH(B2:F2),0))}

as an array formula (CTRL+SHIFT+ENTER), where you put the month number in A2. You have to format the cell with a date format. I tested this and it returned the correct value for months 1-5.

Upvotes: 1

Related Questions