Vlookup with a function?

mashley

New Member
Joined
Sep 29, 2006
Messages
5
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.
 

Some videos you may like

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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Jul 12, 2006
Messages
1,141
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
Joined
Sep 29, 2006
Messages
5
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
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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
Joined
Jul 12, 2006
Messages
1,141
Does the master look like this
[1]
A B C
Product 2006 2005

or
[2]
Product
2006
2005

If looks like [1]

just adjust the formula to this:

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

mashley

New Member
Joined
Sep 29, 2006
Messages
5

ADVERTISEMENT

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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Sep 29, 2006
Messages
5
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
Joined
Jul 12, 2006
Messages
1,141
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,
 

Watch MrExcel Video

Forum statistics

Threads
1,108,634
Messages
5,523,996
Members
409,555
Latest member
TIPSAREA

This Week's Hot Topics

Top