Find Given Date Between Fiscal Years and Return Year

[SuziBaz]

I have a Training Spreadsheet which houses all training courses held for a company throughout the year.
I want to be able to filter (slicer) by year, so I have created a table of fiscal years (dates) and the year that I would like to return as below:

Fiscal Start Fiscal End Return this Value
Column X Column Y Column Z
01-Oct-14 30-Sep-15 "2014 - 15"
01-Oct-15 30-Sep-16 "2015 - 16"
01-Oct-16 30-Sep-17 "2016 - 17"
01-Oct-17 30-Sep-18 "2017 - 18"
01-Oct-18 30-Sep-19 "2018 - 19"
01-Oct-19 30-Sep-20 "2019 - 20"

The training date is in column B and I wish to return the value in column A

​I have tried a variety of IF AND combinations but cannot find the correct formula

 

[NARAYANK991]

Hi ,

If you have already set up your table the way you have posted it , then a simple VLOOKUP can return the fiscal year text string ; for instance , I copied your data into the range $E$5:$G$10 ; I removed the quotes from the fiscal year text strings.

Now , if you have any date in the period 01-Oct-2014 through 30-Sep-2020 , using the following formula will return the corresponding fiscal year text string :

=VLOOKUP(date value,$E$5:$G$10,3,TRUE)

where date value would refer to the worksheet cell which has the data value for which you want the fiscal year.

 

[SuziBaz]

WOW I didn't even think about a VLOOKUP formula, thank you NARAYANK991

 

[AlanY]

try this

Excel 2013/2016
	A	B	X	Y	Z
1	year	date
2	2014 - 15	09-Jan-15	01-Oct-14	30-Sep-15	2014 - 15
3	2014 - 15	08-Jun-15	01-Oct-15	30-Sep-16	2015 - 16
4	2015 - 16	05-Nov-15	01-Oct-16	30-Sep-17	2016 - 17
5	2015 - 16	03-Apr-16	01-Oct-17	30-Sep-18	2017 - 18
6	2015 - 16	31-Aug-16	01-Oct-18	30-Sep-19	2018 - 19
7	2016 - 17	28-Jan-17	01-Oct-19	30-Sep-20	2019 - 20
Sheet2

Cell Formulas
Range	Formula
A2	=INDEX($Z$1:$Z$7,SUMPRODUCT(ROW($A$1:$A$7),(B2>=$X$1:$X$7)*(B2<=$Y$1:$Y$7)))

 

[SuziBaz]

Thanks AlanY....all sorted now.Brilliant responses thank you all