Unlucky
Board Regular
- Joined
- Dec 3, 2014
- Messages
- 58
- Office Version
- 2016
- Platform
- Windows
I'm trying to report a percentage from a table, based on Month & Year and Program identification criteria. I am currently using the formula: {=INDEX($Q$2:$Q$49,MATCH(1,($O$2:$O$49=B$3)*($P$2:$P$49=$G$1)),0)} but what is returned is not even close.
Of note, the reporting table is set up so that the Program and calendar year can be changed by a drop down menu.
Bottom Line: the Index & Match formula is not reporting the correct information for each month in a calendar year.
Here is the reporting table:
Of note, the reporting table is set up so that the Program and calendar year can be changed by a drop down menu.
Bottom Line: the Index & Match formula is not reporting the correct information for each month in a calendar year.
Here is the reporting table:
| TEST LOG FOR FORMULAS.xlsx | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 1 | B | ||||||||||||||
| 2 | 100% | JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC | ||
| 3 | CY | JAN - 22 | FEB - 22 | MAR - 22 | APR - 22 | MAY - 22 | JUN - 22 | JUL - 22 | AUG - 22 | SEP - 22 | OCT - 22 | NOV - 22 | DEC - 22 | ||
| 4 | 2022 | 40% | 40% | 40% | 40% | 40% | 40% | 40% | 40% | 40% | 40% | 40% | 79% | ||
| 5 | 2021 | 40% | 40% | 40% | 40% | 40% | 22% | 40% | 40% | 45% | 40% | 43% | 40% | ||
| 6 | JAN - 21 | FEB - 21 | MAR - 21 | APR - 21 | MAY - 21 | JUN - 21 | JUL - 21 | AUG - 21 | SEP - 21 | OCT - 21 | NOV - 21 | DEC - 21 | |||
| 7 | 22 | 0% | 0% | 0% | 0% | 0% | 100% | 0% | 0% | -100% | 0% | -100% | 100% | ||
| 8 | 21 | ||||||||||||||
| 9 | PROGRAM | A | YEAR | 2021 | |||||||||||
| 10 | B | 2022 | |||||||||||||
| 11 | C | 2023 | |||||||||||||
| 12 | D | 2024 | |||||||||||||
| 13 | E | 2025 | |||||||||||||
FORMULA TEST | |||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B3:M3 | B3 | =B2&" - "&$A$7 |
| B4:M4 | B4 | =INDEX($Q$2:$Q$49,MATCH(1,($O$2:$O$49=B$3)*($P$2:$P$49=$G$1)),0) |
| B5:M5 | B5 | =INDEX($Q$2:$Q$49,MATCH(1,($O$2:$O$49=B$6)*($P$2:$P$49=$G$1)),0) |
| B6:M6 | B6 | =B2&" - "&$A$8 |
| B7:M7 | B7 | =SIGN(B4-B5) |
| A5 | A5 | =(A4)-1 |
| A7:A8 | A7 | =RIGHT(A4,2) |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| B7:M7 | Other Type | Icon set | NO | |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| A4 | List | =$E$9:$E$13 |
| G1 | List | =$B$9:$B$13 |
