VLookup issue using pivot table data

**hsmith** · 02-20-2012, 02:33 PM

I have this report I have been working on for a very long time and have made many changes over the past months to this report. It seems everytime I make changes I create more problems for myself. I am trying to eliminate some problems with improving formulas in the excel workbook. I am using 2010 and have never used the vlookup function. I will try to explain as clear as possible what I am trying to do.

I have data in a pivot table called 'PivotTable2' has data in range A15:E28 (Column A has a month in it)
I have a table in range G15:J28
I have a control pivot table on a separate sheet called 'PivotTable4' has data in range A9:E22 (column A had a month in it that matches that of the PivotTable2)

I have been using this formula '=IF(ISBLANK(B15),"",B15/Control!B9)'
In this formula the Control part is the name of the sheet of the control pivot table.

I need to take the above formula and convert it to using vlookup and have no clue how to go about doing this.

**arlu1201** · 02-20-2012, 02:42 PM

Why dont you attach a sample sheet with some dummy data, which has the same format as your original file?

**hsmith** · 02-20-2012, 02:58 PM

Here is a basic example of what I am using.

**hsmith** · 02-20-2012, 04:55 PM

With looking and helping other people with simplier issues I was able to figure this one out. I am just posting what I came up with just in case someone else runs into the same problem I was having. Below is how the function looks that I used to get the value I was needing.

=IF(ISBLANK(B5),"",VLOOKUP(A5,A5:E9,2,TRUE)/VLOOKUP(A5,Control!A5:E9,2,TRUE))