HI All,
I have below index match function but return with #REF! error message, i have evaulate Formula and it seems locating to the correct cell, but why it shows #REF! message?
I have below index match function but return with #REF! error message, i have evaulate Formula and it seems locating to the correct cell, but why it shows #REF! message?
Liebherr Demand Forecast 08.04.22.xlsx | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||
3 | Product | Product Desc | Product Depa | Year | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | ||
4 | ECBN5066 | ECBN5066-23 136 75cm INTEGBIOFRESH FRID FREEZ RH HINGE | 3PRO | 2020 | 0 | 0 | 0 | 16 | 32 | 60 | 21 | 7 | 8 | 10 | 32 | 14 | ||
5 | 0% | 0% | 0% | 20% | 0% | 0% | 0% | 0% | 20% | 0% | 0% | 0% | ||||||
6 | ECBN5066 | 2021 | 5 | 35 | 18 | 22 | 11 | 19 | 20 | 10 | 7 | 11 | 15 | 20 | ||||
7 | 0% | 0% | 0% | 20% | 0% | 0% | 0% | 0% | 20% | 0% | 0% | 0% | ||||||
8 | ECBN5066 | 2022 | 25 | 17 | 30 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||||
9 | 0% | 0% | 0% | 20% | 0% | 0% | 0% | 0% | 20% | 0% | 0% | 0% | ||||||
10 | ECBN5066 | 2023 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||||
11 | 0% | 0% | 0% | 20% | 0% | 0% | 0% | 0% | 20% | 0% | 0% | 0% | ||||||
Dashboard Monthly |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A4 | A4 | ='Product list'!A2 |
B4 | B4 | =XLOOKUP(A4,'Product list'!A:A,'Product list'!B:B) |
C4 | C4 | =VLOOKUP(A4,'Product list'!A:C,3,FALSE) |
E4:P4 | E4 | =SUMIFS('History Month'!B:B,'History Month'!$A:$A,'Dashboard Monthly'!$A4) |
E5:P5 | E5 | =IFERROR(INDEX('Promo Grid'!C:C,MATCH('Dashboard Monthly'!$A4,'Promo Grid'!$A:$A,0)),"") |
E6:P6 | E6 | =SUMIFS('History Month'!N:N,'History Month'!$A:$A,'Dashboard Monthly'!$A4) |
E7:P7 | E7 | =IFERROR(INDEX('Promo Grid'!O:O,MATCH('Dashboard Monthly'!$A4,'Promo Grid'!$A:$A,0)),"") |
E8:P8 | E8 | =SUMIFS('History Month'!Z:Z,'History Month'!$A:$A,'Dashboard Monthly'!$A4) |
E9:P9 | E9 | =IFERROR(INDEX('Promo Grid'!AA:AA,MATCH('Dashboard Monthly'!$A4,'Promo Grid'!$A:$A,0)),"") |
E10:P10 | E10 | =SUMIFS('History Month'!AL:AL,'History Month'!$A:$A,'Dashboard Monthly'!$A4) |
E11:P11 | E11 | =IFERROR(INDEX('Promo Grid'!AM:AM,MATCH('Dashboard Monthly'!$A4,'Promo Grid'!$A:$A,0)),"") |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
'Product list'!_FilterDatabase | ='Product list'!$A$1:$C$62 | B4:C4 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
E9:P9 | Other Type | DataBar | NO | |
E7:P7 | Other Type | DataBar | NO | |
E11:P11 | Other Type | DataBar | NO | |
E5:P5 | Other Type | DataBar | NO |
Liebherr Demand Forecast 08.04.22.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | 2022 | 2022 | 2022 | 2022 | 2022 | |||
2 | Item | Apr | May | Jun | Jul | Aug | ||
3 | ECBN5066 | #REF! | #REF! | #REF! | #REF! | #REF! | ||
Forecast |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A3 | A3 | ='Product list'!A2 |
B3:F3 | B3 | =INDEX('Dashboard Monthly'!$E$4:$P$1568,MATCH(Forecast!$A3,'Dashboard Monthly'!$A:$A,0),MATCH(Forecast!$B$2,'Dashboard Monthly'!$E$3:$P$3,0),MATCH(Forecast!$B$1,'Dashboard Monthly'!$D:$D,0)) |