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).
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
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.
Solution vlookup formula
So using the standard structure of
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)
Which produces the result in our data as follows
… and enables us to compete the summed invoiced amounts by Quarter on the Summary tab using a SUMIF formula
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:
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.