Vlookup? Index/Match?

ctsammon

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)

Nick Korna

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!