VLOOKUP is a very handy tools in Ms Excel. I once wrote about it in my old blog: here. Let’s write that again for a slightly different case. Suppose you have a daily data in a monthly table for many years, and you want to change the table into annually. Of course we don’t want to copy and paste it one by one because it will make us crazy.

So here is the lazy guide on how to do that utilizing what excel already give us.

the case

So we want to get the value in the yellow column. First, we need to prepare the first column (the date). Then the “month” and “day” column to help defining the VLOOKUP.

For the second and third column (month and day):

=month(P3)
=day(P3)

the above formula will give us the month and day from the first column. Then, for the yellow column:

=VLOOKUP(R3,$A$3:$M$33,Q3+1,FALSE)

let me explain the Vlookup formula as follow

vlookup explanation

Enjoy!

Leave a Reply

Your email address will not be published.