# Vlookup with a function?

#### mashley

##### New Member
I have several lists of products with the number of sales for each product. The first sheet is a total for several teams, the subsequent sheets are the individual teams. I need to be able to calculate, on the team sheets, the % of each product that each team is contributing to the total number. Each list is constanly changing and the products dont appear on the same row in each sheet. I am thinking something like vlookup but instead of a colum index number as the output, I need to enter the division forumla. Thanks.

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### barry houdini

##### MrExcel MVP
Hello, welcome to the board

If, in all your sheets you have products in column A and sales in B then in each team sheet use this formula in C2 copied down

=B2/VLOOKUP(A2,master!A\$2:B\$100,2,0)

format as percentage

where master is the name of your totals sheet

#### seenfresh

##### Well-known Member
Looks good!

could also use this it's the same thing but if you were to insert any more columns in between A & B for other criterias the result vector would adjust accordingly

=B2/VLOOKUP(A2,master!A\$2:B\$100,A:B,0)

#### mashley

##### New Member
Much thanks, almost there.
Let me give the rest of the problem...
Each product has two rows of data, 2006 on the row with the product name and the 2005 on the next row with no product name repeated. So on the individual sheets I can change the cell being divided but how do I adjust so that the I can divide by the 2005 row on the master sheet.

#### barry houdini

##### MrExcel MVP

Try this revised formula for C2 copied down

=B2/INDEX(Master!B:B,MATCH(IF(A2="",A1,A2),A:A,0)+(A2=""))

#### seenfresh

##### Well-known Member
Does the master look like this

A B C
Product 2006 2005

or

Product
2006
2005

If looks like 

just adjust the formula to this:

=B2/VLOOKUP(A2,master!A\$2:C\$100,A:C,0)

#### mashley

##### New Member

it looks like:

column a column b column c
Product 1 2006 25
2005 22
Product 2 2006 30
2005 28

The data in all sheets is coming from pivot tables and I have added in column d on the team sheets the fomula to show their % of the total.

#### barry houdini

##### MrExcel MVP
it looks like:

column a column b column c
Product 1 2006 25
2005 22
Product 2 2006 30
2005 28

The data in all sheets is coming from pivot tables and I have added in column d on the team sheets the fomula to show their % of the total.

Then use (adjusted) formula as above for D2 copied down

=C2/INDEX(Master!C:C,MATCH(IF(A2="",A1,A2),A:A,0)+(A2=""))

#### mashley

##### New Member
Regarding: =B2/INDEX(Master!B:B,MATCH(IF(A2="",A1,A2),A:A,0)+(A2=""))

I tried to convert to my actual columns and went down in flames, true columns are:
formula in T
Number being divided in R
Number divided by in R
Product name in C

#### seenfresh

##### Well-known Member
it looks like:

column a column b column c
Product 1 2006 25
2005 22
Product 2 2006 30
2005 28

The data in all sheets is coming from pivot tables and I have added in column d on the team sheets the fomula to show their % of the total.

Then use (adjusted) formula as above for D2 copied down

=C2/INDEX(Master!C:C,MATCH(IF(A2="",A1,A2),A:A,0)+(A2=""))

Barry here is my understanding: I have a couple question in end, if you could give some understanding that would be awesome!

INDEX(array,row_num,column_num) as the main argument

Array=Master!C:C
row_num=MATCH(IF(A2="",A1,A2),A:A,0)+(A2="")
we don't need a column_num array is in only column 3
MATCH(lookup_value,lookup_array,match_type)
Thus IF(logical_test,value_if_true,value_if_false)

IF(A2="",A1,A2)=Lookupvalue,A:A=lookuparray,0=matchtype)

Barry Because array is held constant in row 3 C:C then we don't need to stipulate the column_num array in the INDEX argument?

Also IF(A2="",A1,A2) refers to if A2="" does this refer to if A2=blank cell?

Thanks,

Replies
2
Views
85
Replies
0
Views
35
Replies
1
Views
33
Replies
0
Views
64
Replies
6
Views
90