Fiscal Year "lookup" formula request

Amanda Carta

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 :(

Paul Newcome

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))

Paul Newcome

Happy to help. 👍️

Paul Newcome

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))

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!

Amanda Carta

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

Happy to help. 👍️