Excel Formula help, IFS function

[MSTwork]

I am trying to write an IFS statement to determine if a date falls within a certain date range.

Date Range: is in Sheet 2 (see below)

	A	B	C
1	FY18	7/1/2017	6/30/2018
2	FY19	7/1/2018	6/30/2019
3	FY20	7/1/2019	6/30/2020

I have written the formula to check if the date in cell I22 is greater than or equal to the date in Sheet 2 Cell B1, less than or equal to date in Sheet 2 Cell C1, if yes return "FY18". I repeat this for all scenarios but it is not working. Can someone help?

=IFS(I22>=Sheet2!$B$1,"FY18",(I22<=Sheet2!$C$1),"FY18",IF(I22>=Sheet2!$B$2),"FY19",IF(I22<=Sheet2!$C$2),"FY19",IF(I22>=Sheet2!$B$3),"FY20",IF(I22<=Sheet2!$C$3),"FY20")

[#2]

@MSTwork If your FY are fixed on those dates, you can use this (no need for the table):

="FY" &YEAR(I22)-2000+IF(MONTH(I22)>6,1,0)

OR copy column A over to D and use vlookup with partial match

=VLOOKUP(I22,B1:D3,3, TRUE)

[#3]

@wsantos 

FY isn't fixed on those dates. The date could fall between those date ranges and if it does I want the true statement to be FY18, FY19, or FY20 depending on what date is in I22.

I don't think the Vlookup will work. 

[#4]

vlookup is using a partial match. Just try it.

@MSTwork 

[#5]

@MSTwork 

Perhaps

=INDEX(Sheet2!$A:$A,MATCH(1,INDEX(($I$22>=Sheet2!$B:$B)*($I$22<=Sheet2!$C:$C),0),0))

[#6]

@Sergei Baklan  no luck :(

[#7]

@Sergei Baklan 

My original formula is working, however it only returns the result "FY18" even if the date falls in one of the other fiscal years.

[#8]

@MSTwork 

Please check attached file

[#9]

@Sergei Baklan that's another way to do it, but the vlookup also works, or better yet, the new xlookup - no need to move the columns around:

=XLOOKUP(H21,B1:B3,A1:A3,"N/A",-1)

You only need to compare one column.

[#10]

@wsantos 

Yes, correct, but that's only if the date is in defined financial years. If search for date from future, e.g in year 2022, such formula returns FY20 for our sample.

By the way, I don't see why in such case XLOOKUP is better than simple LOOKUP

=LOOKUP(I22,Sheet2!$B:$B,Sheet2!$A:$A)

[#11]

@MSTwork 

Upon analyzing your formula, I believe you need an AND statement in your if statement. For example for row 1, your formula would look like:

=IF(AND(I22>=Sheet2!B1,I22<=Sheet2!C1),Sheet2!A1," ")

Basically, it displays the value FY18 if true, else display space(" ") if false. 

[#12]

@Sergei Baklan 

This worked! Thank you so much!!

[#13]

@MSTwork , you are welcome