It’s simple enough to group our data by calendar year in a pivot or using the YEAR() function but often we need to group data by financial year. Financial year start dates vary by geography and by company. This tutorial will show you how to add financial year labels to your data based on your date criteria. Follow along in the Financial Year Tutorial (US Dates).
Example Financial Year VLOOKUP Scenario
In the example, we would like to populate column C in the Data tab so that there is a Financial Year associated with each Invoice Date (in column A).
By including the Financial Year in the data, it will enable us to include an automatic summary table (see Summary tab in the Tutorial Workbook) and will allow us to fill in the red gap shown below
In order to do this, we will need to create a lookup table which in the example file is done on the Lookup tab. Importantly, the lookup values in column A are the start dates for each Financial Year listed in Column B.
Solution vlookup formula
So using the standard structure of =VLOOKUP ( SearchFor , WhereToSearch , WhichColumn ,NearOrExact ) where
SearchFor is the Invoice Date (cell A2)
WhereToSearch is the list of Dates and Financial Years (Lookup Tab A:B)
Which column is the second column (2)
NearOrExact is Near (True)
Our formula looks like =VLOOKUP(A2,Lookup!A:B,2,TRUE)
This results in the Financial Year column being populated
… and enables us to compete the summed invoiced amounts by Financial Year on the Summary tab using a SUMIF formula
UK date format version to follow!
Want to get good at using the VLOOKUP function?
Download our practical VLOOKUP tutorial workbook with step-by-step instructions and examples to help you become a vlookup pro.