Index - Match Issue

[ProWoods]

Good morning,

I have a Spreadsheet that has 557 Rows, Columns A-I, the Index - Match works fine, but I added a Row, Updated my Index and Match, but it does not find the Data in Row 558. It is Not a Table, has No Define Name Range, Calculations are set to Automatic. No matter what I have tried, including re-typing the formula in a different cell, it will not look past Row 557.

The Formula: {=INDEX(C2:C558, MATCH(1, (K2=E2:E558)*(L2=F2:F558)*(M2=G2:G558),0),1)}

Yes, I did the Ctrl+Shift+Enter.

 

[arthurbr]

it will not look past Row 557

Could you please explain what you mean?

 

[awoohaw]

Maybe you need to set the references as absolute?:

=INDEX($C$2:$C$558, MATCH(1, ($K$2=$E$2:$E$558)*(L2=$F$2:$F$558)*(M2=$G$2:$G$558),0),1)

also since you have 365, why not use XLOOKUP (this is whinged, but, I hope you understand where I'm going if I have an argument placed incorrectly.)

=XLOOKUP(1,(K2=$E$2:$E$558)*(L2=$F$2:$F$558)*(M2=$G$2:$G$558),$C$2:$C$558,1,0)

 

[ProWoods]

Maybe you need to set the references as absolute?:

=INDEX($C$2:$C$558, MATCH(1, ($K$2=$E$2:$E$558)*(L2=$F$2:$F$558)*(M2=$G$2:$G$558),0),1)

also since you have 365, why not use XLOOKUP (this is whinged, but, I hope you understand where I'm going if I have an argument placed incorrectly.)

=XLOOKUP(1,(K2=$E$2:$E$558)*(L2=$F$2:$F$558)*(M2=$G$2:$G$558),$C$2:$C$558,1,0)

I have just tried both of you suggestions, in the Index Match, no change, Using XLookUp, gives me a #NAME?. Using Absolute in both situation makes no difference.

 

[ProWoods]

Could you please explain what you mean?

The Index Match will pull the Data on Row 557, but will not pull the Data from Row 558

 

[ProWoods]

The Index Match will pull the Data on Row 557, but will not pull the Data from Row 558

... or below Row 557... like 558, 559, etc.

 

[awoohaw]

I have just tried both of you suggestions, in the Index Match, no change, Using XLookUp, gives me a #NAME?. Using Absolute in both situation makes no difference.

i suggest posting a small sample of your data using the xl2bb add in or share a sanitized version of your workbook. The formulas below work. You could close the workbook and see if it may still cause the errors.
Are you sure that the criteria you're entering matches an entire ROW for all three columns?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	INDEX MATCH	48	1		CTK	FIO	QFK				ZRX	PEA	KPS
3	XLOOKUP	48	2		TKH	EQJ	UZB
4			3		JCD	TFB	BAG
5			4		BLL	JRU	TGR
6			5		IDM	DTQ	DMN
7			6		JNB	EXG	DPD
8			7		XHX	SQV	GTW
9			8		CZK	AVM	DKZ
10			9		WGG	ZGS	PQB
11			10		HNH	FTX	EHQ
12			11		SBD	VFN	URE
13			12		XIL	FFH	FRW
14			13		ZWF	RCN	MFX
15			14		TQC	BSN	YAT
16			15		QAU	HHB	DEY
17			16		FKR	XNC	OGN
18			17		FAL	WUR	FQD
19			18		EMJ	FGS	SXD
20			19		VDT	FJK	VLC
21			20		SDQ	VSE	UCA
22			21		FLK	LWY	BXK
23			22		IMP	MYA	UQL
24			23		FTA	YMU	OQS
25			24		DAU	YOB	GNE
26			25		WQH	FGW	NJK
27			26		OJJ	LXG	QKM
28			27		CXO	NOR	WLR
29			28		PZE	QDT	FJR
30			29		FRU	HKO	SBU
31			30		MFL	WNO	XQF
32			31		OAI	TNA	YWZ
33			32		FAE	FZX	ICA
34			33		SXL	AVT	KRZ
35			34		FCG	UCA	YJK
36			35		KEJ	WLQ	FNG
37			36		IED	ZVF	BVP
38			37		XJN	YTP	IPK
39			38		KWN	CPN	OET
40			39		WTS	LOZ	CAJ
41			40		JAN	DGB	ICD
42			41		GRE	JPL	ZUC
43			42		JBG	EUX	ECN
44			43		BPW	ONE	ZUR
45			44		SNM	AXH	RWI
46			45		SEY	KGC	EGZ
47			46		OFU	KWO	XRH
48			47		DKW	XOH	LQI
49			48		ZRX	PEA	KPS
Sheet1

Cell Formulas
Range	Formula
B2	B2	=INDEX(C2:C49, MATCH(1, (K2=$E$2:$E$49)*(L2=$F$2:$F$49)*(M2=$G$2:$G$49),0),1)
B3	B3	=XLOOKUP(1,(K2=$E$2:$E$49)*(L2=$F$2:$F$49)*(M2=$G$2:$G$49),$C$2:$C$49,"not found",0)

 

[ProWoods]

I have just tried both of you suggestions, in the Index Match, no change, Using XLookUp, gives me a #NAME?. Using Absolute in both situation makes no difference.

I am running Pro 2016

 

[ProWoods]

i suggest posting a small sample of your data using the xl2bb add in or share a sanitized version of your workbook. The formulas below work. You could close the workbook and see if it may still cause the errors.

Are you sure that the criteria you're entering matches an entire ROW for all three columns?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	INDEX MATCH	48	1		CTK	FIO	QFK				ZRX	PEA	KPS
3	XLOOKUP	48	2		TKH	EQJ	UZB
4			3		JCD	TFB	BAG
5			4		BLL	JRU	TGR
6			5		IDM	DTQ	DMN
7			6		JNB	EXG	DPD
8			7		XHX	SQV	GTW
9			8		CZK	AVM	DKZ
10			9		WGG	ZGS	PQB
11			10		HNH	FTX	EHQ
12			11		SBD	VFN	URE
13			12		XIL	FFH	FRW
14			13		ZWF	RCN	MFX
15			14		TQC	BSN	YAT
16			15		QAU	HHB	DEY
17			16		FKR	XNC	OGN
18			17		FAL	WUR	FQD
19			18		EMJ	FGS	SXD
20			19		VDT	FJK	VLC
21			20		SDQ	VSE	UCA
22			21		FLK	LWY	BXK
23			22		IMP	MYA	UQL
24			23		FTA	YMU	OQS
25			24		DAU	YOB	GNE
26			25		WQH	FGW	NJK
27			26		OJJ	LXG	QKM
28			27		CXO	NOR	WLR
29			28		PZE	QDT	FJR
30			29		FRU	HKO	SBU
31			30		MFL	WNO	XQF
32			31		OAI	TNA	YWZ
33			32		FAE	FZX	ICA
34			33		SXL	AVT	KRZ
35			34		FCG	UCA	YJK
36			35		KEJ	WLQ	FNG
37			36		IED	ZVF	BVP
38			37		XJN	YTP	IPK
39			38		KWN	CPN	OET
40			39		WTS	LOZ	CAJ
41			40		JAN	DGB	ICD
42			41		GRE	JPL	ZUC
43			42		JBG	EUX	ECN
44			43		BPW	ONE	ZUR
45			44		SNM	AXH	RWI
46			45		SEY	KGC	EGZ
47			46		OFU	KWO	XRH
48			47		DKW	XOH	LQI
49			48		ZRX	PEA	KPS
Sheet1

Cell Formulas
Range	Formula
B2	B2	=INDEX(C2:C49, MATCH(1, (K2=$E$2:$E$49)*(L2=$F$2:$F$49)*(M2=$G$2:$G$49),0),1)
B3	B3	=XLOOKUP(1,(K2=$E$2:$E$49)*(L2=$F$2:$F$49)*(M2=$G$2:$G$49),$C$2:$C$49,"not found",0)

Yes, the Rows Index and Match, I can Upload the complete Workbook, it is just a Color Chart for Access.

 

[ProWoods]

Yes, the Rows Index and Match, I can Upload the complete Workbook, it is just a Color Chart for Access.

It will not do any good to use xl2bb, it cannot Upload a Complete Sheet Range. I have not issues from Row 1 to Row 557, it is just below that range that seems to have an issue.