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.

 

[ProWoods]

share a sanitized version of your workbook

How do I "share a sanitized version of your workbook"?

 

[ProWoods]

I just deleted some Duplicate Entries, bringing the Row Count down to 556, the Index Match still will not find the Last Row or beyond. I have tried to Insert a Row in the middle of what I already have, it will not find that Row either. Very strange???

 

[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)

I Fixed it, it was Data Type. Why it has been working, I do not know, but, the Data was set to Text, but the Lookup Cells were set to General, I did not catch that! Thank you for trying to help!!