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
-
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))
-
Happy to help. 👍️
Answers
-
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))
-
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 :)
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!