I have an Index + Match formula working on a table with sorting options.
In terms of the file, when working normally, it is supposed to return a certain set of values depending on what "Stage" is, and returns a value in the corresponding row/column based on what I+M returns. But when I sort away from standard naming (Stage > A to Z), the entire formula breaks, leaving me with either wrong references or #N/As. I am not sure what causes this, as even Formula Auditing > Evaluate Formula gives the wrong results in this case.
For example, when sorting by [Time > A to Z], when Stage = "0-2", the Match formula correctly identifies Row 20, but for some reason Match on Stages "0-3" and "0-4" also return Row 20, when they should be 38 and 40 respectively. Other times, the Match formula simply returns #N/A, even though the specified Stage exists in the G column.
Wondering if there is a fix to get I+M working on tables in different sorting configurations, or if there is a similar formula/combination to get it working as intended.
Sheet download link: Loading Google Sheets
Mini-sheet, maybe this will work for those who don't want to download?
In terms of the file, when working normally, it is supposed to return a certain set of values depending on what "Stage" is, and returns a value in the corresponding row/column based on what I+M returns. But when I sort away from standard naming (Stage > A to Z), the entire formula breaks, leaving me with either wrong references or #N/As. I am not sure what causes this, as even Formula Auditing > Evaluate Formula gives the wrong results in this case.
For example, when sorting by [Time > A to Z], when Stage = "0-2", the Match formula correctly identifies Row 20, but for some reason Match on Stages "0-3" and "0-4" also return Row 20, when they should be 38 and 40 respectively. Other times, the Match formula simply returns #N/A, even though the specified Stage exists in the G column.
Wondering if there is a fix to get I+M working on tables in different sorting configurations, or if there is a similar formula/combination to get it working as intended.
Sheet download link: Loading Google Sheets
Mini-sheet, maybe this will work for those who don't want to download?
| Girls Frontline checklist.xlsx | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 1 | MP | AMMO | RATN | PART | Stage | MP | AMMO | RATN | PART | Time | |||||
| 2 | 0-1 | #N/A | #N/A | #N/A | #N/A | 1-1 | 10 | 30 | 15 | 0 | 0.25 | ||||
| 3 | 0-2 | 183.33 | 0.00 | 0.00 | 116.67 | 3-1 | 50 | 0 | 75 | 0 | 0.33 | ||||
| 4 | 0-3 | 183.33 | 0.00 | 0.00 | 116.67 | 1-2 | 0 | 40 | 60 | 0 | 0.50 | ||||
| 5 | 0-4 | 183.33 | 0.00 | 0.00 | 116.67 | 5-1 | 0 | 0 | 100 | 45 | 0.50 | ||||
| 6 | Total/hr | #N/A | #N/A | #N/A | #N/A | 9-1 | 0 | 0 | 100 | 50 | 0.50 | ||||
| 7 | 2-1 | 100 | 0 | 0 | 30 | 0.67 | |||||||||
| 8 | MP | AMMO | RATN | PART | 3-2 | 0 | 120 | 70 | 30 | 0.75 | |||||
| 9 | 0-1 | #N/A | #N/A | #N/A | #N/A | 0-1 | 0 | 145 | 145 | 0 | 0.83 | ||||
| 10 | 0-2 | 550 | 0 | 0 | 350 | 1-3 | 30 | 0 | 30 | 10 | 1.00 | ||||
| 11 | 0-3 | 550 | 0 | 0 | 350 | 4-1 | 0 | 185 | 185 | 0 | 1.00 | ||||
| 12 | 0-4 | 550 | 0 | 0 | 350 | 8-1 | 150 | 150 | 150 | 0 | 1.00 | ||||
| 13 | Total | #N/A | #N/A | #N/A | #N/A | 2-2 | 60 | 200 | 80 | 0 | 1.50 | ||||
| 14 | 3-3 | 0 | 300 | 0 | 0 | 1.50 | |||||||||
| 15 | 9-2 | 180 | 0 | 180 | 100 | 1.50 | |||||||||
| 16 | 1-4 | 160 | 160 | 0 | 0 | 2.00 | |||||||||
| 17 | 4-2 | 0 | 0 | 0 | 210 | 2.00 | |||||||||
| 18 | 6-1 | 300 | 300 | 0 | 100 | 2.00 | |||||||||
| 19 | 5-2 | 0 | 600 | 300 | 0 | 2.50 | |||||||||
| 20 | 7-1 | 650 | 0 | 650 | 0 | 2.50 | |||||||||
| 21 | 0-2 | 550 | 0 | 0 | 350 | 3.00 | |||||||||
| 22 | 6-2 | 0 | 200 | 550 | 100 | 3.00 | |||||||||
| 23 | 8-2 | 0 | 0 | 0 | 450 | 3.00 | |||||||||
| 24 | 2-3 | 10 | 10 | 10 | 230 | 4.00 | |||||||||
| 25 | 5-3 | 800 | 400 | 400 | 400 | 4.00 | |||||||||
| 26 | 7-2 | 0 | 650 | 0 | 300 | 4.00 | |||||||||
| 27 | 9-3 | 750 | 750 | 0 | 0 | 4.50 | |||||||||
| 28 | 3-4 | 0 | 0 | 300 | 300 | 5.00 | |||||||||
| 29 | 6-3 | 0 | 0 | 200 | 500 | 5.00 | |||||||||
| 30 | 7-3 | 900 | 600 | 600 | 0 | 5.50 | |||||||||
| 31 | 2-4 | 0 | 250 | 600 | 60 | 6.00 | |||||||||
| 32 | 4-3 | 800 | 550 | 0 | 0 | 6.00 | |||||||||
| 33 | 8-3 | 400 | 800 | 800 | 0 | 6.00 | |||||||||
| 34 | 5-4 | 100 | 0 | 0 | 700 | 7.00 | |||||||||
| 35 | 9-4 | 500 | 900 | 900 | 0 | 7.00 | |||||||||
| 36 | 4-4 | 400 | 400 | 400 | 150 | 8.00 | |||||||||
| 37 | 7-4 | 250 | 250 | 250 | 600 | 8.00 | |||||||||
| 38 | 8-4 | 1500 | 400 | 400 | 100 | 9.00 | |||||||||
| 39 | 0-3 | 900 | 900 | 900 | 250 | 12.00 | |||||||||
| 40 | 6-4 | 800 | 800 | 800 | 0 | 12.00 | |||||||||
| 41 | 0-4 | 0 | 1200 | 800 | 750 | 24.00 | |||||||||
| 42 | |||||||||||||||
Man LogCalcHR | |||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2:B3 | B2 | =INDEX($H$2:$K$41,MATCH($A2,$G$2:$G$41),1)/INDEX($H$2:$L$41,MATCH($A2,$G$2:$G$41),5) |
| C2:C3 | C2 | =INDEX($H$2:$L$41,MATCH($A2,$G$2:$G$41),2)/INDEX($H$2:$L$41,MATCH($A2,$G$2:$G$41),5) |
| D2:D3 | D2 | =INDEX($H$2:$L$41,MATCH($A2,$G$2:$G$41),3)/INDEX($H$2:$L$41,MATCH($A2,$G$2:$G$41),5) |
| E2:E3 | E2 | =INDEX($H$2:$L$41,MATCH($A2,$G$2:$G$41),4)/INDEX($H$2:$L$41,MATCH($A2,$G$2:$G$41),5) |
| B4:B5 | B4 | =IF(ISBLANK(#REF!),0,INDEX($H$2:$K$41,MATCH($A4,$G$2:$G$41),1)/INDEX($H$2:$L$41,MATCH($A4,$G$2:$G$41),5)) |
| C4:C5 | C4 | =IF(ISBLANK(#REF!),0,INDEX($H$2:$L$41,MATCH($A4,$G$2:$G$41),2)/INDEX($H$2:$L$41,MATCH($A4,$G$2:$G$41),5)) |
| D4:D5 | D4 | =IF(ISBLANK(#REF!),0,INDEX($H$2:$L$41,MATCH($A4,$G$2:$G$41),3)/INDEX($H$2:$L$41,MATCH($A4,$G$2:$G$41),5)) |
| E4:E5 | E4 | =IF(ISBLANK(#REF!),0,INDEX($H$2:$L$41,MATCH($A4,$G$2:$G$41),4)/INDEX($H$2:$L$41,MATCH($A4,$G$2:$G$41),5)) |
| B6:E6,B13:E13 | B6 | =SUM(B2:B5) |
| L3 | L3 | =2/6 |
| B9:B12 | B9 | =IF(ISBLANK($A9),0,INDEX($H$2:$L$41,MATCH($A9,$G$2:$G$41),1)) |
| C9:C12 | C9 | =IF(ISBLANK($A9),0,INDEX($H$2:$L$41,MATCH($A9,$G$2:$G$41),2)) |
| D9:D12 | D9 | =IF(ISBLANK($A9),0,INDEX($H$2:$L$41,MATCH($A9,$G$2:$G$41),3)) |
| E9:E12 | E9 | =IF(ISBLANK($A9),0,INDEX($H$2:$L$41,MATCH($A9,$G$2:$G$41),4)) |
| L7 | L7 | =40/60 |
| L8 | L8 | =45/60 |
| L9 | L9 | =50/60 |
| A9:A12 | A9 | =A2 |
