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.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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=""))
 
Upvote 0
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
 
Upvote 0
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,
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top