Multi-conditional Index/Match Function

Laviah01

New Member
Joined
May 8, 2019
Messages
3
Hello,

Hoping that someone could assist!

I have a workbook where I needed to use the index/match function to source the data. While I was able to successfully complete the formula, I need that the formula also takes into consideration the dates.

So for example, if the source data has excluded February 7th and 8th data, but my standard file has columns to represent February 7th and 8th, I just want to report zero for those periods. I initially tried to also incorporate the "IF" function with the Index/Match function but was unsuccessful, I need the formula to account for any missing data points, any suggestions?

See sample formula as follows: =INDEX('Source Data'!B4:B22,MATCH("Pear 1",'Source Data'!$A$4:$A$22,0))

Source Data
Excel 2016 (Windows) 32 bit
ABCDEFGHIJKLMNOPQ
11-Feb-194-Feb-195-Feb-196-Feb-1911-Feb-1912-Feb-1913-Feb-1914-Feb-1915-Feb-1919-Feb-1920-Feb-1922-Feb-1925-Feb-1926-Feb-1927-Feb-19
2
3
4Pear 1 770.0 1,670.0 880.0 1,100.0 760.0 900.0 1,080.0 1,070.0 810.0 880.0 310.0 760.0 1,100.0 900.0 730.0
5Pear 2 90.0 60.0 40.0 40.0 720.0 30.0 20.0 20.0 60.0 470.0 1,430.0 720.0 40.0 40.0 30.0
6Pear 3 10.0 70.0 40.0 10.0 20.0 60.0 50.0 70.0 80.0 10.0 20.0 20.0 10.0 10.0 80.0
7Pear 4 - 4,240.0 940.0 - 1,150.0 - 260.0 1,110.0 950.0 - 1,800.0 1,150.0 - 1,850.0 1,740.0
8Pear 5 - 530.0 - 10.0 150.0 560.0 110.0 6,770.0 150.0 - 170.0 150.0 10.0 1,810.0 240.0
9Pear 6 654.5 1,419.5 748.0 935.0 646.0 765.0 918.0 909.5 688.5 748.0 263.5 646.0 935.0 765.0 620.5
10Pear 7 76.5 51.0 34.0 34.0 612.0 25.5 17.0 17.0 51.0 399.5 1,215.5 612.0 34.0 34.0 25.5
11Pear 8 8.5 59.5 34.0 8.5 17.0 51.0 42.5 59.5 68.0 8.5 17.0 17.0 8.5 8.5 68.0
12Pear 9 - 3,604.0 799.0 - 977.5 - 221.0 943.5 807.5 - 1,530.0 977.5 - 1,572.5 1,479.0
13Pear 10 - 450.5 - 8.5 127.5 476.0 93.5 5,754.5 127.5 - 144.5 127.5 8.5 1,538.5 204.0
14Pear 11 556.3 1,206.6 635.8 794.8 549.1 650.3 780.3 773.1 585.2 635.8 224.0 549.1 794.8 650.3 527.4
15Pear 12 65.0 43.4 28.9 28.9 520.2 21.7 14.5 14.5 43.4 339.6 1,033.2 520.2 28.9 28.9 21.7
16Pear 13 7.2 50.6 28.9 7.2 14.5 43.4 36.1 50.6 57.8 7.2 14.5 14.5 7.2 7.2 57.8
17Pear 14 - 3,063.4 679.2 - 830.9 - 187.9 802.0 686.4 - 1,300.5 830.9 - 1,336.6 1,257.2
18Pear 15 - 382.9 - 7.2 108.4 404.6 79.5 4,891.3 108.4 - 122.8 108.4 7.2 1,307.7 173.4
19Pear 16 472.9 1,025.6 540.4 675.5 466.7 552.7 663.3 657.1 497.4 540.4 190.4 466.7 675.5 552.7 448.3
20Pear 17 55.3 36.8 24.6 24.6 442.2 18.4 12.3 12.3 36.8 288.6 878.2 442.2 24.6 24.6 18.4
21Pear 18 6.1 43.0 24.6 6.1 12.3 36.8 30.7 43.0 49.1 6.1 12.3 12.3 6.1 6.1 49.1
22Pear 19 - 2,603.9 577.3 - 706.2 - 159.7 681.7 583.4 - 1,105.4 706.2 - 1,136.1 1,068.6
23
24
25 The source data is missing certain periods, but the standard sheet still has those missing dates…February 7th and 8th is missing from the source data, but these will still be in the standard sheet.

<tbody>
</tbody>
Source Data





Standard Template
Excel 2016 (Windows) 32 bit
ABCDEFGHIJKLMNOPQRSTUV
11-Feb-194-Feb-195-Feb-196-Feb-197-Feb-198-Feb-1911-Feb-1912-Feb-1913-Feb-1914-Feb-1915-Feb-1918-Feb-1919-Feb-1920-Feb-1921-Feb-1922-Feb-1925-Feb-1926-Feb-1927-Feb-1928-Feb-19 Total
2
3 Technically the 7th and the 8th are missing from the source data, I need the formula to report zeros should any data point be missing from the source file.
4Section 11336.332946.581555.801904.751329.101610.251910.301913.081475.231525.80553.981329.101904.751560.251337.430.000.000.000.000.00 24,193
5Pear 1 770.00 1,670.00 880.00 1,100.00 760.00 900.00 1,080.00 1,070.00 810.00 880.00 310.00 760.00 1,100.00 900.00 730.00 - - - - - 13,720
6Pear 3 10.00 70.00 40.00 10.00 20.00 60.00 50.00 70.00 80.00 10.00 20.00 20.00 10.00 10.00 80.00 - - - - - 560
7Pear 11 556.33 1,206.58 635.80 794.75 549.10 650.25 780.30 773.08 585.23 635.80 223.98 549.10 794.75 650.25 527.43 - - - - - 9,913
8
9
10Section 2166.503624.90753.1582.502290.38531.50318.356593.48924.88869.504090.502290.3882.502949.131516.650.000.000.000.000.00 27,084
11Pear 14 - 3,063.40 679.15 - 830.88 - 187.85 801.98 686.38 - 1,300.50 830.88 - 1,336.63 1,257.15 - - - - - 10,975
12Pear 2 90.00 60.00 40.00 40.00 720.00 30.00 20.00 20.00 60.00 470.00 1,430.00 720.00 40.00 40.00 30.00 - - - - - 3,810
13Pear 7 76.50 51.00 34.00 34.00 612.00 25.50 17.00 17.00 51.00 399.50 1,215.50 612.00 34.00 34.00 25.50 - - - - - 3,239
14Pear 10 - 450.50 - 8.50 127.50 476.00 93.50 5,754.50 127.50 - 144.50 127.50 8.50 1,538.50 204.00 - - - - - 9,061

<tbody>
</tbody>
Standard Sheet

Worksheet Formulas
CellFormula
B4=SUM(B5:B7)
C4=SUM(C5:C7)
D4=SUM(D5:D7)
E4=SUM(E5:E7)
F4=SUM(F5:F7)
G4=SUM(G5:G7)
H4=SUM(H5:H7)
I4=SUM(I5:I7)
J4=SUM(J5:J7)
K4=SUM(K5:K7)
L4=SUM(L5:L7)
M4=SUM(M5:M7)
N4=SUM(N5:N7)
O4=SUM(O5:O7)
P4=SUM(P5:P7)
Q4=SUM(Q5:Q7)
R4=SUM(R5:R7)
S4=SUM(S5:S7)
T4=SUM(T5:T7)
U4=SUM(U5:U7)
V4=SUM(B4:U4)
B5=INDEX('Source Data'!B4:B22,MATCH([COLOR=#0]"Pear 1",'Source Data'!$A$4:$A$22,0)[/COLOR])
C5=INDEX('Source Data'!C4:C22,MATCH([COLOR=#0]"Pear 1",'Source Data'!$A$4:$A$22,0)[/COLOR])
D5=INDEX('Source Data'!D4:D22,MATCH([COLOR=#0]"Pear 1",'Source Data'!$A$4:$A$22,0)[/COLOR])
E5=INDEX('Source Data'!E4:E22,MATCH([COLOR=#0]"Pear 1",'Source Data'!$A$4:$A$22,0)[/COLOR])
F5=INDEX('Source Data'!F4:F22,MATCH([COLOR=#0]"Pear 1",'Source Data'!$A$4:$A$22,0)[/COLOR])
G5=INDEX('Source Data'!G4:G22,MATCH([COLOR=#0]"Pear 1",'Source Data'!$A$4:$A$22,0)[/COLOR])
H5=INDEX('Source Data'!H4:H22,MATCH([COLOR=#0]"Pear 1",'Source Data'!$A$4:$A$22,0)[/COLOR])
I5=INDEX('Source Data'!I4:I22,MATCH([COLOR=#0]"Pear 1",'Source Data'!$A$4:$A$22,0)[/COLOR])
J5=INDEX('Source Data'!J4:J22,MATCH([COLOR=#0]"Pear 1",'Source Data'!$A$4:$A$22,0)[/COLOR])
K5=INDEX('Source Data'!K4:K22,MATCH([COLOR=#0]"Pear 1",'Source Data'!$A$4:$A$22,0)[/COLOR])
L5=INDEX('Source Data'!L4:L22,MATCH([COLOR=#0]"Pear 1",'Source Data'!$A$4:$A$22,0)[/COLOR])
M5=INDEX('Source Data'!M4:M22,MATCH([COLOR=#0]"Pear 1",'Source Data'!$A$4:$A$22,0)[/COLOR])
N5=INDEX('Source Data'!N4:N22,MATCH([COLOR=#0]"Pear 1",'Source Data'!$A$4:$A$22,0)[/COLOR])
O5=INDEX('Source Data'!O4:O22,MATCH([COLOR=#0]"Pear 1",'Source Data'!$A$4:$A$22,0)[/COLOR])
P5=INDEX('Source Data'!P4:P22,MATCH([COLOR=#0]"Pear 1",'Source Data'!$A$4:$A$22,0)[/COLOR])
Q5=INDEX('Source Data'!Q4:Q22,MATCH([COLOR=#0]"Pear 1",'Source Data'!$A$4:$A$22,0)[/COLOR])
R5=INDEX('Source Data'!R4:R22,MATCH([COLOR=#0]"Pear 1",'Source Data'!$A$4:$A$22,0)[/COLOR])
S5=INDEX('Source Data'!S4:S22,MATCH([COLOR=#0]"Pear 1",'Source Data'!$A$4:$A$22,0)[/COLOR])
T5=INDEX('Source Data'!T4:T22,MATCH([COLOR=#0]"Pear 1",'Source Data'!$A$4:$A$22,0)[/COLOR])
U5=INDEX('Source Data'!U4:U22,MATCH([COLOR=#0]"Pear 1",'Source Data'!$A$4:$A$22,0)[/COLOR])
V5=SUM(B5:U5)
B6=INDEX('Source Data'!B4:B22,MATCH([COLOR=#0]"Pear 3",'Source Data'!$A$4:$A$22,0)[/COLOR])
C6=INDEX('Source Data'!C4:C22,MATCH([COLOR=#0]"Pear 3",'Source Data'!$A$4:$A$22,0)[/COLOR])
D6=INDEX('Source Data'!D4:D22,MATCH([COLOR=#0]"Pear 3",'Source Data'!$A$4:$A$22,0)[/COLOR])
E6=INDEX('Source Data'!E4:E22,MATCH([COLOR=#0]"Pear 3",'Source Data'!$A$4:$A$22,0)[/COLOR])
F6=INDEX('Source Data'!F4:F22,MATCH([COLOR=#0]"Pear 3",'Source Data'!$A$4:$A$22,0)[/COLOR])
G6=INDEX('Source Data'!G4:G22,MATCH([COLOR=#0]"Pear 3",'Source Data'!$A$4:$A$22,0)[/COLOR])
H6=INDEX('Source Data'!H4:H22,MATCH([COLOR=#0]"Pear 3",'Source Data'!$A$4:$A$22,0)[/COLOR])
I6=INDEX('Source Data'!I4:I22,MATCH([COLOR=#0]"Pear 3",'Source Data'!$A$4:$A$22,0)[/COLOR])
J6=INDEX('Source Data'!J4:J22,MATCH([COLOR=#0]"Pear 3",'Source Data'!$A$4:$A$22,0)[/COLOR])
K6=INDEX('Source Data'!K4:K22,MATCH([COLOR=#0]"Pear 3",'Source Data'!$A$4:$A$22,0)[/COLOR])
L6=INDEX('Source Data'!L4:L22,MATCH([COLOR=#0]"Pear 3",'Source Data'!$A$4:$A$22,0)[/COLOR])
M6=INDEX('Source Data'!M4:M22,MATCH([COLOR=#0]"Pear 3",'Source Data'!$A$4:$A$22,0)[/COLOR])
N6=INDEX('Source Data'!N4:N22,MATCH([COLOR=#0]"Pear 3",'Source Data'!$A$4:$A$22,0)[/COLOR])
O6=INDEX('Source Data'!O4:O22,MATCH([COLOR=#0]"Pear 3",'Source Data'!$A$4:$A$22,0)[/COLOR])
P6=INDEX('Source Data'!P4:P22,MATCH([COLOR=#0]"Pear 3",'Source Data'!$A$4:$A$22,0)[/COLOR])
Q6=INDEX('Source Data'!Q4:Q22,MATCH([COLOR=#0]"Pear 3",'Source Data'!$A$4:$A$22,0)[/COLOR])
R6=INDEX('Source Data'!R4:R22,MATCH([COLOR=#0]"Pear 3",'Source Data'!$A$4:$A$22,0)[/COLOR])
S6=INDEX('Source Data'!S4:S22,MATCH([COLOR=#0]"Pear 3",'Source Data'!$A$4:$A$22,0)[/COLOR])
T6=INDEX('Source Data'!T4:T22,MATCH([COLOR=#0]"Pear 3",'Source Data'!$A$4:$A$22,0)[/COLOR])
U6=INDEX('Source Data'!U4:U22,MATCH([COLOR=#0]"Pear 3",'Source Data'!$A$4:$A$22,0)[/COLOR])
V6=SUM(B6:U6)
B7=INDEX('Source Data'!B4:B22,MATCH([COLOR=#0]"Pear 11",'Source Data'!$A$4:$A$22,0)[/COLOR])
C7=INDEX('Source Data'!C4:C22,MATCH([COLOR=#0]"Pear 11",'Source Data'!$A$4:$A$22,0)[/COLOR])
D7=INDEX('Source Data'!D4:D22,MATCH([COLOR=#0]"Pear 11",'Source Data'!$A$4:$A$22,0)[/COLOR])
E7=INDEX('Source Data'!E4:E22,MATCH([COLOR=#0]"Pear 11",'Source Data'!$A$4:$A$22,0)[/COLOR])
F7=INDEX('Source Data'!F4:F22,MATCH([COLOR=#0]"Pear 11",'Source Data'!$A$4:$A$22,0)[/COLOR])
G7=INDEX('Source Data'!G4:G22,MATCH([COLOR=#0]"Pear 11",'Source Data'!$A$4:$A$22,0)[/COLOR])
H7=INDEX('Source Data'!H4:H22,MATCH([COLOR=#0]"Pear 11",'Source Data'!$A$4:$A$22,0)[/COLOR])
I7=INDEX('Source Data'!I4:I22,MATCH([COLOR=#0]"Pear 11",'Source Data'!$A$4:$A$22,0)[/COLOR])
J7=INDEX('Source Data'!J4:J22,MATCH([COLOR=#0]"Pear 11",'Source Data'!$A$4:$A$22,0)[/COLOR])
K7=INDEX('Source Data'!K4:K22,MATCH([COLOR=#0]"Pear 11",'Source Data'!$A$4:$A$22,0)[/COLOR])
L7=INDEX('Source Data'!L4:L22,MATCH([COLOR=#0]"Pear 11",'Source Data'!$A$4:$A$22,0)[/COLOR])
M7=INDEX('Source Data'!M4:M22,MATCH([COLOR=#0]"Pear 11",'Source Data'!$A$4:$A$22,0)[/COLOR])
N7=INDEX('Source Data'!N4:N22,MATCH([COLOR=#0]"Pear 11",'Source Data'!$A$4:$A$22,0)[/COLOR])
O7=INDEX('Source Data'!O4:O22,MATCH([COLOR=#0]"Pear 11",'Source Data'!$A$4:$A$22,0)[/COLOR])
P7=INDEX('Source Data'!P4:P22,MATCH([COLOR=#0]"Pear 11",'Source Data'!$A$4:$A$22,0)[/COLOR])
Q7=INDEX('Source Data'!Q4:Q22,MATCH([COLOR=#0]"Pear 11",'Source Data'!$A$4:$A$22,0)[/COLOR])
R7=INDEX('Source Data'!R4:R22,MATCH([COLOR=#0]"Pear 11",'Source Data'!$A$4:$A$22,0)[/COLOR])
S7=INDEX('Source Data'!S4:S22,MATCH([COLOR=#0]"Pear 11",'Source Data'!$A$4:$A$22,0)[/COLOR])
T7=INDEX('Source Data'!T4:T22,MATCH([COLOR=#0]"Pear 11",'Source Data'!$A$4:$A$22,0)[/COLOR])
U7=INDEX('Source Data'!U4:U22,MATCH([COLOR=#0]"Pear 11",'Source Data'!$A$4:$A$22,0)[/COLOR])
V7=SUM(B7:U7)
B10=SUM(B11:B14)
C10=SUM(C11:C14)
D10=SUM(D11:D14)
E10=SUM(E11:E14)
F10=SUM(F11:F14)
G10=SUM(G11:G14)
H10=SUM(H11:H14)
I10=SUM(I11:I14)
J10=SUM(J11:J14)
K10=SUM(K11:K14)
L10=SUM(L11:L14)
M10=SUM(M11:M14)
N10=SUM(N11:N14)
O10=SUM(O11:O14)
P10=SUM(P11:P14)
Q10=SUM(Q11:Q14)
R10=SUM(R11:R14)
S10=SUM(S11:S14)
T10=SUM(T11:T14)
U10=SUM(U11:U14)
V10=SUM(B10:U10)
B11=INDEX('Source Data'!B4:B22,MATCH([COLOR=#0]"Pear 14",'Source Data'!$A$4:$A$22,0)[/COLOR])
C11=INDEX('Source Data'!C4:C22,MATCH([COLOR=#0]"Pear 14",'Source Data'!$A$4:$A$22,0)[/COLOR])
D11=INDEX('Source Data'!D4:D22,MATCH([COLOR=#0]"Pear 14",'Source Data'!$A$4:$A$22,0)[/COLOR])
E11=INDEX('Source Data'!E4:E22,MATCH([COLOR=#0]"Pear 14",'Source Data'!$A$4:$A$22,0)[/COLOR])
F11=INDEX('Source Data'!F4:F22,MATCH([COLOR=#0]"Pear 14",'Source Data'!$A$4:$A$22,0)[/COLOR])
G11=INDEX('Source Data'!G4:G22,MATCH([COLOR=#0]"Pear 14",'Source Data'!$A$4:$A$22,0)[/COLOR])
H11=INDEX('Source Data'!H4:H22,MATCH([COLOR=#0]"Pear 14",'Source Data'!$A$4:$A$22,0)[/COLOR])
I11=INDEX('Source Data'!I4:I22,MATCH([COLOR=#0]"Pear 14",'Source Data'!$A$4:$A$22,0)[/COLOR])
J11=INDEX('Source Data'!J4:J22,MATCH([COLOR=#0]"Pear 14",'Source Data'!$A$4:$A$22,0)[/COLOR])
K11=INDEX('Source Data'!K4:K22,MATCH([COLOR=#0]"Pear 14",'Source Data'!$A$4:$A$22,0)[/COLOR])
L11=INDEX('Source Data'!L4:L22,MATCH([COLOR=#0]"Pear 14",'Source Data'!$A$4:$A$22,0)[/COLOR])
M11=INDEX('Source Data'!M4:M22,MATCH([COLOR=#0]"Pear 14",'Source Data'!$A$4:$A$22,0)[/COLOR])
N11=INDEX('Source Data'!N4:N22,MATCH([COLOR=#0]"Pear 14",'Source Data'!$A$4:$A$22,0)[/COLOR])
O11=INDEX('Source Data'!O4:O22,MATCH([COLOR=#0]"Pear 14",'Source Data'!$A$4:$A$22,0)[/COLOR])
P11=INDEX('Source Data'!P4:P22,MATCH([COLOR=#0]"Pear 14",'Source Data'!$A$4:$A$22,0)[/COLOR])
Q11=INDEX('Source Data'!Q4:Q22,MATCH([COLOR=#0]"Pear 14",'Source Data'!$A$4:$A$22,0)[/COLOR])
R11=INDEX('Source Data'!R4:R22,MATCH([COLOR=#0]"Pear 14",'Source Data'!$A$4:$A$22,0)[/COLOR])
S11=INDEX('Source Data'!S4:S22,MATCH([COLOR=#0]"Pear 14",'Source Data'!$A$4:$A$22,0)[/COLOR])
T11=INDEX('Source Data'!T4:T22,MATCH([COLOR=#0]"Pear 14",'Source Data'!$A$4:$A$22,0)[/COLOR])
U11=INDEX('Source Data'!U4:U22,MATCH([COLOR=#0]"Pear 14",'Source Data'!$A$4:$A$22,0)[/COLOR])
V11=SUM(B11:U11)
B12=INDEX('Source Data'!B4:B22,MATCH([COLOR=#0]"Pear 2",'Source Data'!$A$4:$A$22,0)[/COLOR])
C12=INDEX('Source Data'!C4:C22,MATCH([COLOR=#0]"Pear 2",'Source Data'!$A$4:$A$22,0)[/COLOR])
D12=INDEX('Source Data'!D4:D22,MATCH([COLOR=#0]"Pear 2",'Source Data'!$A$4:$A$22,0)[/COLOR])
E12=INDEX('Source Data'!E4:E22,MATCH([COLOR=#0]"Pear 2",'Source Data'!$A$4:$A$22,0)[/COLOR])
F12=INDEX('Source Data'!F4:F22,MATCH([COLOR=#0]"Pear 2",'Source Data'!$A$4:$A$22,0)[/COLOR])
G12=INDEX('Source Data'!G4:G22,MATCH([COLOR=#0]"Pear 2",'Source Data'!$A$4:$A$22,0)[/COLOR])
H12=INDEX('Source Data'!H4:H22,MATCH([COLOR=#0]"Pear 2",'Source Data'!$A$4:$A$22,0)[/COLOR])
I12=INDEX('Source Data'!I4:I22,MATCH([COLOR=#0]"Pear 2",'Source Data'!$A$4:$A$22,0)[/COLOR])
J12=INDEX('Source Data'!J4:J22,MATCH([COLOR=#0]"Pear 2",'Source Data'!$A$4:$A$22,0)[/COLOR])
K12=INDEX('Source Data'!K4:K22,MATCH([COLOR=#0]"Pear 2",'Source Data'!$A$4:$A$22,0)[/COLOR])
L12=INDEX('Source Data'!L4:L22,MATCH([COLOR=#0]"Pear 2",'Source Data'!$A$4:$A$22,0)[/COLOR])
M12=INDEX('Source Data'!M4:M22,MATCH([COLOR=#0]"Pear 2",'Source Data'!$A$4:$A$22,0)[/COLOR])
N12=INDEX('Source Data'!N4:N22,MATCH([COLOR=#0]"Pear 2",'Source Data'!$A$4:$A$22,0)[/COLOR])
O12=INDEX('Source Data'!O4:O22,MATCH([COLOR=#0]"Pear 2",'Source Data'!$A$4:$A$22,0)[/COLOR])
P12=INDEX('Source Data'!P4:P22,MATCH([COLOR=#0]"Pear 2",'Source Data'!$A$4:$A$22,0)[/COLOR])
Q12=INDEX('Source Data'!Q4:Q22,MATCH([COLOR=#0]"Pear 2",'Source Data'!$A$4:$A$22,0)[/COLOR])
R12=INDEX('Source Data'!R4:R22,MATCH([COLOR=#0]"Pear 2",'Source Data'!$A$4:$A$22,0)[/COLOR])
S12=INDEX('Source Data'!S4:S22,MATCH([COLOR=#0]"Pear 2",'Source Data'!$A$4:$A$22,0)[/COLOR])
T12=INDEX('Source Data'!T4:T22,MATCH([COLOR=#0]"Pear 2",'Source Data'!$A$4:$A$22,0)[/COLOR])
U12=INDEX('Source Data'!U4:U22,MATCH([COLOR=#0]"Pear 2",'Source Data'!$A$4:$A$22,0)[/COLOR])
V12=SUM(B12:U12)
B13=INDEX('Source Data'!B4:B22,MATCH([COLOR=#0]"Pear 7",'Source Data'!$A$4:$A$22,0)[/COLOR])
C13=INDEX('Source Data'!C4:C22,MATCH([COLOR=#0]"Pear 7",'Source Data'!$A$4:$A$22,0)[/COLOR])
D13=INDEX('Source Data'!D4:D22,MATCH([COLOR=#0]"Pear 7",'Source Data'!$A$4:$A$22,0)[/COLOR])
E13=INDEX('Source Data'!E4:E22,MATCH([COLOR=#0]"Pear 7",'Source Data'!$A$4:$A$22,0)[/COLOR])
F13=INDEX('Source Data'!F4:F22,MATCH([COLOR=#0]"Pear 7",'Source Data'!$A$4:$A$22,0)[/COLOR])
G13=INDEX('Source Data'!G4:G22,MATCH([COLOR=#0]"Pear 7",'Source Data'!$A$4:$A$22,0)[/COLOR])
H13=INDEX('Source Data'!H4:H22,MATCH([COLOR=#0]"Pear 7",'Source Data'!$A$4:$A$22,0)[/COLOR])
I13=INDEX('Source Data'!I4:I22,MATCH([COLOR=#0]"Pear 7",'Source Data'!$A$4:$A$22,0)[/COLOR])
J13=INDEX('Source Data'!J4:J22,MATCH([COLOR=#0]"Pear 7",'Source Data'!$A$4:$A$22,0)[/COLOR])
K13=INDEX('Source Data'!K4:K22,MATCH([COLOR=#0]"Pear 7",'Source Data'!$A$4:$A$22,0)[/COLOR])
L13=INDEX('Source Data'!L4:L22,MATCH([COLOR=#0]"Pear 7",'Source Data'!$A$4:$A$22,0)[/COLOR])
M13=INDEX('Source Data'!M4:M22,MATCH([COLOR=#0]"Pear 7",'Source Data'!$A$4:$A$22,0)[/COLOR])
N13=INDEX('Source Data'!N4:N22,MATCH([COLOR=#0]"Pear 7",'Source Data'!$A$4:$A$22,0)[/COLOR])
O13=INDEX('Source Data'!O4:O22,MATCH([COLOR=#0]"Pear 7",'Source Data'!$A$4:$A$22,0)[/COLOR])
P13=INDEX('Source Data'!P4:P22,MATCH([COLOR=#0]"Pear 7",'Source Data'!$A$4:$A$22,0)[/COLOR])
Q13=INDEX('Source Data'!Q4:Q22,MATCH([COLOR=#0]"Pear 7",'Source Data'!$A$4:$A$22,0)[/COLOR])
R13=INDEX('Source Data'!R4:R22,MATCH([COLOR=#0]"Pear 7",'Source Data'!$A$4:$A$22,0)[/COLOR])
S13=INDEX('Source Data'!S4:S22,MATCH([COLOR=#0]"Pear 7",'Source Data'!$A$4:$A$22,0)[/COLOR])
T13=INDEX('Source Data'!T4:T22,MATCH([COLOR=#0]"Pear 7",'Source Data'!$A$4:$A$22,0)[/COLOR])
U13=INDEX('Source Data'!U4:U22,MATCH([COLOR=#0]"Pear 7",'Source Data'!$A$4:$A$22,0)[/COLOR])
V13=SUM(B13:U13)
B14=INDEX('Source Data'!B4:B22,MATCH([COLOR=#0]"Pear 10",'Source Data'!$A$4:$A$22,0)[/COLOR])
C14=INDEX('Source Data'!C4:C22,MATCH([COLOR=#0]"Pear 10",'Source Data'!$A$4:$A$22,0)[/COLOR])
D14=INDEX('Source Data'!D4:D22,MATCH([COLOR=#0]"Pear 10",'Source Data'!$A$4:$A$22,0)[/COLOR])
E14=INDEX('Source Data'!E4:E22,MATCH([COLOR=#0]"Pear 10",'Source Data'!$A$4:$A$22,0)[/COLOR])
F14=INDEX('Source Data'!F4:F22,MATCH([COLOR=#0]"Pear 10",'Source Data'!$A$4:$A$22,0)[/COLOR])
G14=INDEX('Source Data'!G4:G22,MATCH([COLOR=#0]"Pear 10",'Source Data'!$A$4:$A$22,0)[/COLOR])
H14=INDEX('Source Data'!H4:H22,MATCH([COLOR=#0]"Pear 10",'Source Data'!$A$4:$A$22,0)[/COLOR])
I14=INDEX('Source Data'!I4:I22,MATCH([COLOR=#0]"Pear 10",'Source Data'!$A$4:$A$22,0)[/COLOR])
J14=INDEX('Source Data'!J4:J22,MATCH([COLOR=#0]"Pear 10",'Source Data'!$A$4:$A$22,0)[/COLOR])
K14=INDEX('Source Data'!K4:K22,MATCH([COLOR=#0]"Pear 10",'Source Data'!$A$4:$A$22,0)[/COLOR])
L14=INDEX('Source Data'!L4:L22,MATCH([COLOR=#0]"Pear 10",'Source Data'!$A$4:$A$22,0)[/COLOR])
M14=INDEX('Source Data'!M4:M22,MATCH([COLOR=#0]"Pear 10",'Source Data'!$A$4:$A$22,0)[/COLOR])
N14=INDEX('Source Data'!N4:N22,MATCH([COLOR=#0]"Pear 10",'Source Data'!$A$4:$A$22,0)[/COLOR])
O14=INDEX('Source Data'!O4:O22,MATCH([COLOR=#0]"Pear 10",'Source Data'!$A$4:$A$22,0)[/COLOR])
P14=INDEX('Source Data'!P4:P22,MATCH([COLOR=#0]"Pear 10",'Source Data'!$A$4:$A$22,0)[/COLOR])
Q14=INDEX('Source Data'!Q4:Q22,MATCH([COLOR=#0]"Pear 10",'Source Data'!$A$4:$A$22,0)[/COLOR])
R14=INDEX('Source Data'!R4:R22,MATCH([COLOR=#0]"Pear 10",'Source Data'!$A$4:$A$22,0)[/COLOR])
S14=INDEX('Source Data'!S4:S22,MATCH([COLOR=#0]"Pear 10",'Source Data'!$A$4:$A$22,0)[/COLOR])
T14=INDEX('Source Data'!T4:T22,MATCH([COLOR=#0]"Pear 10",'Source Data'!$A$4:$A$22,0)[/COLOR])
U14=INDEX('Source Data'!U4:U22,MATCH([COLOR=#0]"Pear 10",'Source Data'!$A$4:$A$22,0)[/COLOR])
V14=SUM(B14:U14)

<tbody>
</tbody>

<tbody>
</tbody>
 
Last edited by a moderator:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
I think you need an Index Match Match to get the correct value.

Try this in Standard Sheet B5: =IFERROR(INDEX('Source Data'!$A$1:$P$22,MATCH('Standard Sheet'!$A5,'Source Data'!$A$1:$A$22,0),MATCH('Standard Sheet'!B$1,'Source Data'!$A$1:$P$1,0),1),0)

Then drag that formula down and across to populate your section numbers.
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

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