Forum Discussion

MSTwork's avatar
MSTwork
Copper Contributor
Mar 06, 2020

Excel Formula help, IFS function

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)

 ABC
1FY187/1/20176/30/2018
2FY197/1/20186/30/2019
3FY207/1/20196/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")

Formulas and Functions

12 Replies

  • MisterE's avatar
    MisterE
    Copper Contributor
    Mar 07, 2020

    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. 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Mar 06, 2020

    MSTwork 

    Perhaps

    =INDEX(Sheet2!$A:$A,MATCH(1,INDEX(($I$22>=Sheet2!$B:$B)*($I$22<=Sheet2!$C:$C),0),0))
  • wsantos's avatar
    wsantos
    Brass Contributor
    Mar 06, 2020

    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)

     

     

    • MSTwork's avatar
      MSTwork
      Copper Contributor
      Mar 06, 2020

      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. 

       

Resources