VLOOKUP with approximate match - Usage

<<< Click here to go back

I was supposed to prepare a  period wise summary on a data. The data was available in excel format at daily level and summary was required at unequal time periods level :

Definition of time periods :
01-Jan-2014    to  16-Jan-2014       -- Period 1
17-Jan-2014    to  27-Apr-2014      -- Period 2
28-Apr-2014   to  19-Sep-2014      -- Period 3
20-Sep-2014   to  03-Feb-2015      -- Period 4
04-Feb-2015   to  29-Jun-2015      -- Period 5
30-Jun-2015   to  31-Dec-2015      -- Period 6

Click to enlarge
How do you think you would do this ?

I could think of many ways do the task :

1. Grouping dates in Pivot Table to make period column, as shown >

2. By making a separate list of all the dates and corresponding period and then use a vlookup function to populate the "period column" with the  base data. Use pivot table then and summarize.

3. Put filter on the data, selecting corresponding dates period wise and use SUBTOTAL function, to get summary.

All these method are fine as such, but are definitely not the efficient ways to tackle this particular problem.

Why ?

All the above method involve too much manual intervention ... it might be huge task if the time period is even more. Also there is also possibility of human error.

VLOOKUP with approximate match option is here to rescue you for the task. Let's see how easy the task becomes with this.

1. Sort the data in ascending order of date
2 . Make a list in excel with start date and period and then VLOOKUP  with approximate function as shown below :

Once you get the period column, you simply pivot the data to get the period wise summary.

 And we are done  !

Enjoy reading our other articles and stay tuned with ...

Kindly do provide your feedback in the 'Comments' Section and share as much as possible.

No comments:

Post a Comment

Do provide us your feedback, it would help us serve your better.