Fiscal Year "lookup" formula request

Hi :) I'm trying to get this lookup to pull in the FY for us but these always give me a hard time. I have the excel formula but I don't really even understand it so not sure how to translate it to Smartsheet talk.


Excel: =LOOKUP(2,1/((H268>='Lookup Data'!$B$2:$B$21)*(H268<='Lookup Data'!$C$2:$C$21)),'Lookup Data'!$A$2:$A$21)

What I'm trying to do is get a date on a row to bring back the FY of the list I have


Please help me :(

Best Answers

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try something along the lines of

    =INDEX([Fiscal Year]:[Fiscal Year], MATCH(MAX(COLLECT([FY Start]:[FY Start], [FY Start]:[FY Start], @cell<= [Date Test]@row)), [FY Start]:[FY Start], 0))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @Amanda Carta,

    You can use an INDEX COLLECT for this:

    =INDEX(COLLECT([Fiscal Year]:[Fiscal Year], [FY Start]:[FY Start], <=[Date Test]@row, [FY End]:[FY End], >=[Date Test]@row), 1)

    Here it will take the Fiscal Year for the first row which has an start date greater to or equal to the Date Test on the row AND an End Date equal to or less than the Date Test on the row.

    Sample (using European style dates, sorry!):

    Hope this helps, but if I've misunderstood anything or you've any problems/questions then let us know!

  • Thank you BOTH so much! They worked! It wasn't working at first but my dummy self forgot to change the columns to "Dates" vs text lol :)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!