Vlookup? Index/Match?
Need some assistance. Our fiscal months/years are a little wonky :). I have a sheet "Master Fiscal Year Dates" which provides the exact dates for each fiscal year, period, and quarter.
In my accident tracking sheet, I need to pull in the Fiscal Year, the period, and the quarter from the Master Fiscal Year Dates sheet - based on the date of the accident entered in the Accident Tracking sheet.
For example, if I enter a new accident date of 8-28-23, I would like the Fiscal Year, Period, and Quarter columns to automatically populate with the correct Fiscal Year, Period, and Quarter which would be pulled from the master fiscal year dates sheet.
Could someone help with a formula for each of the columns: Fiscal Year, Period, and Quarter?
Grid 1 (Master Fiscal Year Dates)
Grid 2 (Accident Tracking)
Answers
-
Hi @ctsammon,
For this you can use INDEX COLLECT with some cross sheet references. For example, for the Fiscal Year:
=INDEX(COLLECT({Fiscal Year}, {Date From}, @cell <= [Date of Accident]@row, {Date To}, @cell >= [Date of Accident]@row), 1)
For the other columns (Period, Quarter), you then just change the first part of the formula to the relevant column.
Just make sure that all your Date columns are set as dates, as if not then you'll get errors!
Hope this helps, but if you've any problems/questions then just post!
-
PERFECT, thank you so much!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!