Formula to reference cell, match, and offset

duckie225

New Member
Joined
May 24, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Freight Box Program Draft 2.xlsx
ABCDEFGHI
1ZONEFreight Box ShippingZone Shipping costCurrent Fuel SurchargeNumber of Stores in ZoneFreight Box Program NumberFreight Box program Cost totalFreight Box Program retail totalFreight Box program profit total
27Zone Cost Economy$ 280.0048.90%27Program 1DATA RETURN HERE
3-#N/A
4-#N/A
5-#N/A
6-#N/A
7-#N/A
8-#N/A
9-#N/A
10-#N/A
11-#N/A
12-#N/A
13-#N/A
14-#N/A
Cost Roll up
Cell Formulas
RangeFormula
C2C2=INDEX('Programs available'!K3:L9,MATCH(A2,'Programs available'!J3:J9,0),MATCH(B2,'Programs available'!$K$2:$L$2,0))
C3C3=INDEX('Programs available'!K3:L9,MATCH(A3,'Programs available'!J3:J9,0),MATCH(B3,'Programs available'!$K$2:$L$2,0))
C4C4=INDEX('Programs available'!K3:L9,MATCH(A4,'Programs available'!J3:J9,0),MATCH(B4,'Programs available'!$K$2:$L$2,0))
C5C5=INDEX('Programs available'!K3:L9,MATCH(A5,'Programs available'!J3:J9,0),MATCH(B5,'Programs available'!$K$2:$L$2,0))
C6C6=INDEX('Programs available'!K3:L9,MATCH(A6,'Programs available'!J3:J9,0),MATCH(B6,'Programs available'!$K$2:$L$2,0))
C7C7=INDEX('Programs available'!K3:L9,MATCH(A7,'Programs available'!J3:J9,0),MATCH(B7,'Programs available'!$K$2:$L$2,0))
C8C8=INDEX('Programs available'!K3:L9,MATCH(A8,'Programs available'!J3:J9,0),MATCH(B8,'Programs available'!$K$2:$L$2,0))
C9C9=INDEX('Programs available'!K3:L9,MATCH(A9,'Programs available'!J3:J9,0),MATCH(B9,'Programs available'!$K$2:$L$2,0))
C10C10=INDEX('Programs available'!K3:L9,MATCH(A10,'Programs available'!J3:J9,0),MATCH(B10,'Programs available'!$K$2:$L$2,0))
C11C11=INDEX('Programs available'!K3:L9,MATCH(A11,'Programs available'!J3:J9,0),MATCH(B11,'Programs available'!$K$2:$L$2,0))
C12C12=INDEX('Programs available'!K3:L9,MATCH(A12,'Programs available'!J3:J9,0),MATCH(B12,'Programs available'!$K$2:$L$2,0))
C13C13=INDEX('Programs available'!K3:L9,MATCH(A13,'Programs available'!J3:J9,0),MATCH(B13,'Programs available'!$K$2:$L$2,0))
C14C14=INDEX('Programs available'!K3:L9,MATCH(A14,'Programs available'!J3:J9,0),MATCH(B14,'Programs available'!$K$2:$L$2,0))
Cells with Data Validation
CellAllowCriteria
F2:F14ListProgram 1, Program 2, Program 3, Program 4, Program 5
A2:A10List='Programs available'!$J$3:$J$9
B2:B14List='Programs available'!$K$2:$M$2
A11:A29List='Programs available'!#REF!


Freight Box Program Draft 2.xlsx
ABCDEFGH
1Program 1Number of kitsCost of kitTotal Cost KitsMSRPTotal Cost MSRP
2ell58$ 1.00$ 8.00$ 2.00$ 16.00
3ell1020$ 2.00$ 40.00$ 3.00$ 60.00
4ell20$ 3.00$ -$ 4.00$ -
5Totals:28$ 48.00$ 76.00Profit$ 28.00
6
7Program 2Number of kitsCost of kitTotal Cost KitsMSRPTotal Cost MSRP
8ell55$ 1.00$ 5.00$ 2.00$ 10.00
9ell1011$ 2.00$ 22.00$ 3.00$ 33.00
10ell205$ 3.00$ 15.00$ 4.00$ 20.00
11Totals:21$ 42.00$ 63.00Profit$ 21.00
12
13Program 3Number of kitsCost of kitTotal Cost KitsMSRPTotal Cost MSRP
14ell57$ 1.00$ 7.00$ 2.00$ 14.00
15ell1012$ 2.00$ 24.00$ 3.00$ 36.00
16ell204$ 3.00$ 12.00$ 4.00$ 16.00
17Totals:23$ 43.00$ 66.00profit$ 23.00
18
19Program 4Number of kitsCost of kitTotal Cost KitsMSRPTotal Cost MSRP
20ell56$ 1.00$ 6.00$ 2.00$ 12.00
21ell1012$ 2.00$ 24.00$ 3.00$ 36.00
22ell205$ 3.00$ 15.00$ 4.00$ 20.00
23Totals:23$ 45.00$ 68.00profit$ 23.00
24
25Program 5 Number of kitsCost of kitTotal Cost KitsMSRPTotal Cost MSRP
26ell513$ 1.00$ 13.00$ 2.00$ 26.00
27ell1023$ 2.00$ 46.00$ 3.00$ 69.00
28ell20$ 3.00$ -$ 4.00$ -
29Totals:36$ 59.00$ 95.00profit$ 36.00
Programs available
Cell Formulas
RangeFormula
B5,F29,D29,B29,F23,D23,B23,F17,D17,B17,F11,D11,B11,F5,D5B5=SUM(B2:B4)
D2:D4,D26:D28,D20:D22,D14:D16,D8:D10D2=SUM(B2*C2)
F2:F4,F26:F28,F20:F22,F14:F16,F8:F10F2=SUM(B2*E2)
H5,H29,H23,H17,H11H5=F5-D5


Trying to populate the cost of the program on the cost roll up tab. I dont even know if excel can do this or if there's a better way. I want to populate G2 on the cost roll up based on referencing the program selected in F2. I want the information in F2 to reference the programs tab to search for the matching name from F2 and populate the highlighted total data based on the program name. Best i can tell is match offset from googling but this is way above my excel savvy.

please help
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
This could be an option:

Dante Amor
ABCDEFGHI
1ZONEFreight Box ShippingZone Shipping costCurrent Fuel SurchargeNumber of Stores in ZoneFreight Box Program NumberFreight Box program Cost totalFreight Box Program retail totalFreight Box program profit total
27Zone Cost Economy2800.48927Program 148
3Program 242
4Program 343
5Program 445
6Program 559
cost roll up tab
Cell Formulas
RangeFormula
G2:G6G2=INDEX('programs available'!$D$1:$D$29,MATCH("Totals:",INDIRECT("'programs available'!A" & MATCH(F2,'programs available'!$A$1:$A$29,0)+1 & ":A29"),0)+MATCH(F2,'programs available'!$A$1:$A$29,0))
 
Upvote 0
Given the uniform nature of the 'Programs available' sheet, this should do it.
If the programs do not all contain the exact same number of rows then some more representative sample data would be good.

duckie225.xlsm
FG
1Freight Box Program NumberFreight Box program Cost total
2Program 148
3Program 242
4Program 343
5Program 445
6Program 559
Cost Roll up
Cell Formulas
RangeFormula
G2:G6G2=INDEX('Programs available'!D:D,MATCH(F2,'Programs available'!A:A,0)+4)
 
Upvote 0
If the programs do not all contain the exact same number of rows ..
Then you could use this and still avoid the use of volatile functions like OFFSET or INDIRECT.

duckie225.xlsm
ABCD
1Program 1Number of kitsCost of kitTotal Cost Kits
2ell5818
3ell1020240
4ell2030
5Totals:2848
6
7Program 2Number of kitsCost of kitTotal Cost Kits
8ell5515
9ell1011222
10ell205315
11
12
13
14Totals:2142
15
16Program 3Number of kitsCost of kitTotal Cost Kits
17ell5717
18ell1012224
19ell204312
20
21Totals:2343
22
23Program 4Number of kitsCost of kitTotal Cost Kits
24ell5616
25ell1012224
26ell205315
27Totals:2345
28
29Program 5Number of kitsCost of kitTotal Cost Kits
30ell513113
31ell1023246
32ell2030
33Totals:3659
Programs available


duckie225.xlsm
FG
1Freight Box Program NumberFreight Box program Cost total
2Program 148
3Program 242
4Program 343
5Program 445
6Program 559
Cost Roll up (2)
Cell Formulas
RangeFormula
G2:G6G2=VLOOKUP("Totals:",INDEX('Programs available'!A:A,MATCH(F2,'Programs available'!A:A,0)):'Programs available'!D$1000,4,0)
 
Upvote 0
You guys are amazing, I didnt realize excel could do almost anything. I'm looking at all of your forumlas and applying the knowledge going through them. I've tried each one back in my original document and its working great for programs 1-4 but will not acknowledge program 5 even though its in the correct spacing and format as all the other programs. i've tried manipulating certain fields and thought i picked it up at one point but just returning N/A but has no problem if I select back to programs 1-4.

thank you for getting me this far, i appreciate you sharing your knowledge with me. I get a little better every time i analyze the formulas actually working.
 
Upvote 0
Whats even more bizarre is that i just duplicated program 5 below its original position blanked the title out changed the new copy to say program 5 and its syncing beautifully. Any reason why a cell could just be a dead spot like that?
 
Upvote 0
but will not acknowledge program 5

In your original data you have a space to the right of "Program 5", so it does not find it. Just remove that space.
1655132819014.png
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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