Index + Match on a table: when sorting table options, I+M breaks

[1caiser]

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?

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

 

[Fluff]

You haven't specified the final argument in the Match function so it's doing an approximate match & for that to work the data has to be sorted A-Z.
For B2 try it like

=INDEX($H$2:$K$41,MATCH($A2,$G$2:$G$41,0),1)/INDEX($H$2:$L$41,MATCH($A2,$G$2:$G$41,0),5)