Tutorial: How to use VLOOKUP to add financial / fiscal year (U.K. Date Format)

By | September 5, 2016

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 (UK 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).

Financial Year Lookup UK1

 

 

 

 

 

 

 

 

 

 

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

vlookup Financial Year 2

 

 

 

 

 

 

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.

Financial Year Lookup UK2a

 

 

 

 

 

 

 

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)

Financial Year Lookup UK3

 

This results in the Financial Year column being populated

Financial Year Lookup UK4

 

 

 

 

 

 

 

 

 

 

 

 

 

 

… and enables us to compete the summed invoiced amounts by Financial Year on the Summary tab using a SUMIF formula

FY Lookup US5

 

 

 

 

 

 

 

 

 

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.

Leave a Reply

Your email address will not be published. Required fields are marked *