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
<tbody>
</tbody>
Standard Template
Excel 2016 (Windows) 32 bit
<tbody>
</tbody>
<tbody>
</tbody>
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
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | 1-Feb-19 | 4-Feb-19 | 5-Feb-19 | 6-Feb-19 | 11-Feb-19 | 12-Feb-19 | 13-Feb-19 | 14-Feb-19 | 15-Feb-19 | 19-Feb-19 | 20-Feb-19 | 22-Feb-19 | 25-Feb-19 | 26-Feb-19 | 27-Feb-19 | ||
2 | |||||||||||||||||
3 | |||||||||||||||||
4 | Pear 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 | |
5 | Pear 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 | |
6 | Pear 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 | |
7 | Pear 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 | |
8 | Pear 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 | |
9 | Pear 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 | |
10 | Pear 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 | |
11 | Pear 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 | |
12 | Pear 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 | |
13 | Pear 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 | |
14 | Pear 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 | |
15 | Pear 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 | |
16 | Pear 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 | |
17 | Pear 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 | |
18 | Pear 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 | |
19 | Pear 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 | |
20 | Pear 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 | |
21 | Pear 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 | |
22 | Pear 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
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | 1-Feb-19 | 4-Feb-19 | 5-Feb-19 | 6-Feb-19 | 7-Feb-19 | 8-Feb-19 | 11-Feb-19 | 12-Feb-19 | 13-Feb-19 | 14-Feb-19 | 15-Feb-19 | 18-Feb-19 | 19-Feb-19 | 20-Feb-19 | 21-Feb-19 | 22-Feb-19 | 25-Feb-19 | 26-Feb-19 | 27-Feb-19 | 28-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. | |||||||||||||||||||||
4 | Section 1 | 1336.33 | 2946.58 | 1555.80 | 1904.75 | 1329.10 | 1610.25 | 1910.30 | 1913.08 | 1475.23 | 1525.80 | 553.98 | 1329.10 | 1904.75 | 1560.25 | 1337.43 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 24,193 |
5 | Pear 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 |
6 | Pear 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 |
7 | Pear 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 | ||||||||||||||||||||||
10 | Section 2 | 166.50 | 3624.90 | 753.15 | 82.50 | 2290.38 | 531.50 | 318.35 | 6593.48 | 924.88 | 869.50 | 4090.50 | 2290.38 | 82.50 | 2949.13 | 1516.65 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 27,084 |
11 | Pear 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 |
12 | Pear 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 |
13 | Pear 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 |
14 | Pear 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
<tbody> </tbody> |
<tbody>
</tbody>
Last edited by a moderator: