Mar 06 2020 07:55 AM
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")
Mar 06 2020 08:57 AM
@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)
Mar 06 2020 09:33 AM
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.
Mar 06 2020 09:38 AM
vlookup is using a partial match. Just try it.
Mar 06 2020 09:50 AM
Perhaps
=INDEX(Sheet2!$A:$A,MATCH(1,INDEX(($I$22>=Sheet2!$B:$B)*($I$22<=Sheet2!$C:$C),0),0))
Mar 06 2020 10:06 AM
My original formula is working, however it only returns the result "FY18" even if the date falls in one of the other fiscal years.
Mar 06 2020 02:18 PM
Mar 06 2020 02:39 PM
@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.
Mar 06 2020 03:02 PM
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)
Mar 06 2020 07:55 PM
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.
Mar 09 2020 10:01 AM