Help for v-lookup approach instead of manually cell linking approach - Excel 2010

Hi Excel Forum,

1.In the attached Excel file, Table 1 mimics my original large data set
2.The original dataset is having 25 data points under variable Quarter; and 15 villages under each Quarter

Background:
I wanted to make a separate chart for each village, showing how pre and post income is trending with Quarters

I have just created a chart for first 2 villages, namely AA_1 and AA_2 (pl see Chart 1 and 2 in the attached)

To create this chart, I should pre-process data like in Table 2 and Table 3.

Question:
Could anyone help me to create the pre-processed two tables using v-lookup approach. You would see I have created table2 and 3 using a very clumsy manual cell linking approach.

Thank you

Mirisage

here you go:
post_this.xlsx

to get it to work properly, I added a helper column to your table 1 with the formula =A5&B5 in cell C5, then dragged it down then used the vlookup formulas in tables 2 and 3

Did you try Pivot Table Chart

Select range A4:D16 >> Go to Insert Menu Tab >> Under Pivot Table Item click on the drop down and Select Pivot Table Chart

In the Report Filter Select Village >> Axis Field will be Quarter >> and the Values Field will be Pre-Income and Post Income.

Once the Pivot Chart is generated...either on the Pivot Table or the Pivot Chart select the filter for the appropriate villages.

Note that you might need to fill in the quarters on all rows in column A

Hi scott,
Your suggestion is great, thank you very much.

Hi FDibbins,
Your code works very well. Thank you very much.

Hi jubiesxl,
Pivot Table Chart is the exactly what I was trying to ask in my next round of post. Now you have answered it, thank you very much.

Thanks again to every one of you!
Mirisage