The same INDEX/MATCH formual is giving different results, and I can't work out why

[Red over White]

I am trying to develop a new Excel file by simplifying an old one, because too many add-ons on the old one have made it too difficult to make constructive improvements. I have been pulling across worksheets individually, and the tried to make everything consistent. I have come across an issue with the formula on row 23. The formulas are effectively the same, the only difference is the column references. The calculation in row 23 should match the dates on rows 14 and 46, and then make a calculation based on the figures in rows 3 and either rows 14 to 17, depending on the date in row 46. With the exception of column K, the formula is working as intended, and is looking at the right figure in 18 to 21. The K column is using the wrong figure, it should be using 27.96 rather than 42.47. I can't work out why it is doing this and was wondering if there is another way of getting the desired result.

Thanks in advance.

Nick

INDEXMATCH.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
3		204	204	5382	130	1309	171	830	490	92	639	582	1643
4		554.00	4,849.29	184.63	1,775.80	759.34	2,889.00	319.56	562.86	1,921.82	830.75	856.80	304.98
5		16/06/97	30/11/17	06/11/17	02/10/17	19/10/17	02/10/17	13/08/12	18/02/99	25/06/15	01/12/20	10/10/17	29/11/17
6			26.00	12.50	12.50	10.50	12.50	9.95		12.50	12.50	10.50	12.50
7			49.46	49.68	11.54	49.70	24.70	13.26		8.84	26.54		25.05
8			8,187.10
9			01/05/20
10									27/06/17
11									443
12
13		14/05/21	30/03/20	01/04/21	31/01/20	03/02/21	28/05/21	22/06/20	24/02/21	08/04/21	20/03/20	28/02/21	26/05/20
14		10/12/20	08/08/19	08/08/19	19/12/19	30/05/19	03/09/20	10/10/19	20/06/19	25/02/21		30/05/19	19/09/19
15							26/11/20
16
17		08/04/21	28/02/20	26/02/21	27/06/19	17/12/20	13/05/21	07/05/20	16/01/21	13/08/20	27/02/20	02/01/21	16/04/20
18		7.00	90.00	3.00	11.30	8.30	53.00	1.50	13.10	27.96		2.45	1.95
19							25.00
20
21		14.00	190.00	1.00	31.50	21.60	54.00	3.50	26.90	42.47	43.90	3.75	3.54
22		1.0000	0.7705	1.0000	1.0000	1.0000	1.0000	1.0000	1.0000	1.0000	1.0000	0.7501	1.0000
23		28.56	0.00	53.82	0.00	282.74	92.34	0.00	119.17	39.07	0.00	16.37	0.00
24
25		C	D	E	F	G	H	I	J	K	L	M	N
26		A	E	I	M	Q	U	Y	AC	AG	AK	AO	AS
27		B	F	J	N	R	V	Z	AD	AH	AL	AP	AT
28		C	G	K	O	S	W	AA	AE	AI	AM	AQ	AU
29		D	H	L	P	T	X	AB	AF	AJ	AN	AR	AV
30			C
31					S			S	S		S	S	S
32	120
33
34	2		Z
35
36		204	204	5,382	130	1,309	171	830	443	92	639	582	1,643
37		1,130.16	9,968.02	9,998.70	2,332.58	9,999.96	4,977.39	2,675.58	2,493.48	1,789.41	5,347.55	4,997.06	5,048.38
38
39
40
41
42
43
44		14/05/21	00/01/00	01/04/21	31/01/20	03/02/21	28/05/21	22/06/20	24/02/21	08/04/21	20/03/20	28/02/21	26/05/20
45		08/04/21	00/01/00	26/02/21	19/12/19	17/12/20	13/05/21	07/05/20	16/01/21	25/02/21	27/02/20	02/01/21	16/04/20
46	120	08/04/21	00/01/00	26/02/21	00/01/00	17/12/20	13/05/21	00/01/00	16/01/21	25/02/21	00/01/00	02/01/21	00/01/00
47
Stock

Cell Formulas
Range	Formula
C23:N23	C23	=IF(OR(C34="Z",C46=0,C5>C46),0,INDEX(C18:C21,MATCH(C46,C14:C17),FALSE)*C36*C22/100)
C25:N25	C25	='/Users/nickdoyle/Nick''s folder/Filthy lucre/[Finances 2021.xlsm]Base H'!C1
C26:N29	C26	='/Users/nickdoyle/Nick''s folder/Filthy lucre/[Finances 2021.xlsm]Base H'!A13
C32:N32	C32	=IF(OR(C10<=NOW()-INDIRECT("B"&ROW()),C10=0),"","R")
D34	D34	=IF(D8<>0,"Z",INDIRECT("Stock22!"&D27&$B34))
C36:N36	C36	=IF(C11>0,C11,C3)
C37:N37	C37	=SUM(C36*C4)/100+SUM(C6:C7)
C44:N44	C44	=IF(C$34="Z",0,C13)
C45:N45	C45	=IF(C$34="Z",0,MAX(C14:C17))
C46:N46	C46	=IF(MAX(C14:C17)>NOW()-$B46,MAX(C14:C17),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
22:22	Cell Value	=1	text	NO
BF47:XFD47,A47:AW47	Cell Value	=0	text	NO
C41:AW41	Cell Value	between 1 and 10000	text	NO
1:1048576	Cell Value	=0	text	NO

 

[Fluff]

You have a bracket in the wrong place, try

=IF(OR(K34="Z",K46=0,K5>K46),0,INDEX(K18:K21,MATCH(K46,K14:K17,FALSE))*K36*K22/100)

 

[Red over White]

Thanks - that has worked. It has taught me to take a break, as I was nearly on it for ten hours.

 

[Fluff]

You're welcome & thanks for the feedback.