# Fiscal Year "lookup" formula request

Options
✭✭✭

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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!

• ✭✭✭
Options

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

• ✭✭✭✭✭✭