Tutorial: How to use VLOOKUP to add quarters to your date information (U.K. format)

By | November 22, 2016

Often we need to view our data by quarter. For example there may be quarterly sales numbers that you would like to view but currently you only have the date of each sale.
This is possible using a VLOOKUP. Remember the structure is:
=VLOOKUP ( SearchFor , WhereToSearch , WhichColumn , NearOrExact )
Example Quarterly Data VLOOKUP Scenario
In the example, we would like to populate column C in the Data tab so that there is a quarter associated with each Sales Date (in column A).

vlookup-quarters-1

 

 

 

 

 

 

 

 

 

 

 

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 Sales Total column below

vlookup-quarters-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 months for each quarter.

 

vlookup quarters 3

 

 

 

 

 

 

Solution vlookup formula

So using the standard structure of

=VLOOKUP( SearchFor , WhereToSearch , WhichColumn ,NearOrExact )  

where SearchFor is based on the Sales Date.
This will be based on the month part of the date, so our SearchFor term will use the MONTH excel formula -> MONTH(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(MONTH(A2),Lookup!A:B,2,TRUE)

 

vlookup-quarters-4

 

 

 

 

 

 

 

 

 

Which produces the result in our data as follows

 

vlookup-quarters-5

 

 

 

 

 

 

 

 

 

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

vlookup-quarters-6

 

 

 

 

 

 

Other Variations on this VLOOKUP formula

Want to get fancy? You can add year by joining the formula above to another.
For example if you wanted quarter, a dash and then the year, the formula would be as follows:

vlookup-quarters-7

 

 

 

 

 

 

 

Want to get super fancy? You can change this to financial/fiscal year quarters if you combine it with our tutorial here

 

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 *