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))
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!
-
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!
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))
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!
-
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. 👍️
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!