CODEWITHSUNDEEP
excelexcel-formula
gabe17
gabe17

Reputation: 41

Index Match to match date between two dates

I have a date column B that are dates. My Fiscal year begins April 1 and ends March 31, I have my date ranges below:

enter image description here

enter image description here

Here is my formula including named ranges. I'm getting N/A error, and I'm not sure what I'm doing wrong.

{=INDEX(fiscalyear,MATCH(1,(startdate>=B2)*(enddate<=B2),0))}

Upvotes: 0

Views: 4901

Answers (3)

basic
basic

Reputation: 11968

Although both of the previous answers are correct and the formulas are simpler than the original one, I would like to point out where the original problem was - the comparison operators were swapped there.

It was:

{=INDEX(fiscalyear,MATCH(1,(startdate>=B2)*(enddate<=B2),0))}

Must be:

{=INDEX(fiscalyear,MATCH(1,(startdate<=B2)*(enddate>=B2),0))}

Upvotes: 2

cybernetic.nomad
cybernetic.nomad

Reputation: 6368

Since any fiscal year starts right after a fiscal year ends you can just use:

=INDEX(fiscalyear,MATCH(B2,startdate,1))

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152450

You can get rid of the lookup with:

="FY" & YEAR(EOMONTH(B2,9))

Upvotes: 3

Related Questions