Is this the best way to calculate the number of events since the last one?

[JenniferMurphy]

The minisheet below is my attempt to calculate the number of events (not the number of days) that have occurred since the last time that event occurred.

Col B has the events. Col C has the dates when they have occurred. Cols D & E are parts of the solution. D is the row # of this event. E is the row # of the previous occurrence of that event. Col F puts these together and adds IfError. Is there a better way?

Note: This depends on the table being sorted by date (newest to oldest).

Take Function.xlsx
	B	C	D	E	F	G
3	Event	Date	Row #	Row #(Next)	# Events	Comments
4	A	6/10/24	1	4	4	Event A last occurred 4 events ago.
5	B	6/08/24	2	2	2	Event B last occurred 2 events ago.
6	C	6/05/24	3	3	3	Event C last occurred 3 events ago.
7	B	6/02/24	4	5	5	Event B last occurred 5 events ago.
8	A	5/30/24	5	#N/A	n/a	This is the first time event A occurred.
9	C	5/25/24	6	#N/A	n/a	This is the first time event C occurred.
10	D	5/20/24	7	1	1	Event D last occurred 1 event ago.
11	D	5/19/24	8	#N/A	n/a	This is the first time event D occurred.
12	B	5/10/24	9	#N/A	n/a	This is the first time event B occurred.
Count Between

Cell Formulas
Range	Formula
D4:D12	D4	=ROW()-ROW(Table4[[#Headers],[Event]])
E4:E12	E4	=MATCH([@Event],DROP([Event],[@[Row '#]]),0)
F4:F12	F4	=IFERROR(MATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")

 

[shaowu459]

工作簿1.xlsx
	A	B	C	D	E	F	G	H
1
2
3		Event	Date	Row #	Row #(Next)	# Events	Comments
4		A	2024-6-10			4	Event A last occurred 4 events ago.
5		B	2024-6-8			2	Event B last occurred 2 events ago.
6		C	2024-6-5			3	Event C last occurred 3 events ago.
7		B	2024-6-2			5	Event B last occurred 5 events ago.
8		A	2024-5-30			n/a	This is the first time event A occurred.
9		C	2024-5-25			n/a	This is the first time event C occurred.
10		D	2024-5-20			1	Event D last occurred 1 event ago.
11		D	2024-5-19			n/a	This is the first time event D occurred.
12		B	2024-5-10			n/a	This is the first time event B occurred.
13
Sheet1

Cell Formulas
Range	Formula
F4:F12	F4	=IFNA(MATCH(B4,B5:B23,),"n/a")

 

[JenniferMurphy]

Hmmm... I was puzzled when I saw that your solution used sheet cell references rather than table cell references. Is your data in a table? Mine is.

I added your formula to my table and was surprised to see that it got the same answers. But then I noticed that your formulas are not the same in each row. The match range extends below the table. So I added an event letter undeneath the table and, as you can see below, it affects the match resulting in an incorrect result. My formula is not affected. I also experimented with adding rows in various locations. This also caused your formula problems.

This all leads me to ask if this "solution" might have come from some AI bot.

XMatch Function.xlsx
	B	C	D	E	F
3	Event	Date	My Solution	shaowu Solution	shaowu Solution FormulaText
4	A	6/10/24	4	4	=IFNA(MATCH(B4,B5:B$12,),"n/a")
5	B	6/08/24	2	2	=IFNA(MATCH(B5,B6:B13,),"n/a")
6	C	6/05/24	3	3	=IFNA(MATCH(B6,B7:B14,),"n/a")
7	B	6/02/24	5	5	=IFNA(MATCH(B7,B8:B15,),"n/a")
8	A	5/30/24	n/a	n/a	=IFNA(MATCH(B8,B9:B16,),"n/a")
9	C	5/25/24	n/a	n/a	=IFNA(MATCH(B9,B10:B17,),"n/a")
10	D	5/20/24	1	1	=IFNA(MATCH(B10,B11:B18,),"n/a")
11	D	5/19/24	n/a	n/a	=IFNA(MATCH(B11,B12:B19,),"n/a")
12	B	5/10/24	n/a	2	=IFNA(MATCH(B12,B13:B20,),"n/a")
13
14	B
Count Between

Cell Formulas
Range	Formula
D4:D12	D4	=IFERROR(MATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")
E4	E4	=IFNA(MATCH(B4,B5:B$12,),"n/a")
F4:F12	F4	=FORMULATEXT([@[shaowu Solution]])
E5:E12	E5	=IFNA(MATCH(B5,B6:B13,),"n/a")

 

[JenniferMurphy]

Ok. I've done a little more testing. The minisheet below shows my original (Match) solution plus the same solution using XMatch. They both get correct answers if I add a row to the top, middle, or end of the table. The old values (before adding rows) are in the Old Values column. The cells before adding the new rows are greyed out. The ones that were changed are highlighted in yellow. I also included a column for shaowu's solution. It gets the wrong asnwer several times.

I have a couple of questions:

1. Is there any reason to use XMatch rather than Match?
2. Is there many reason to use IFNA rather than IFERROR?
3. Is there a better way do this?

Thanks

XMatch Function.xlsx
	B	C	D	E	F	G	H
3	Event	Date	Old Values	Match Solution	XMatch Solution	Comments	shaowu Solution
4	C	6/11/24		4	4	New C event add at top of table	4
5	A	6/10/24	4	5	5	Result of adding D event in middle of table	5
6	B	6/08/24	2	3	3	Result of adding D event in middle of table	2
7	D	6/07/24		5	5	New D event added in middle of table	5
8	C	6/05/24	3	3	3		3
9	B	6/02/24	5	5	5		5
10	A	5/30/24	n/a	5	5	Result of adding A event at end	5
11	C	5/25/24	n/a	n/a	n/a		n/a
12	D	5/20/24	1	1	1		1
13	D	5/19/24	n/a	n/a	n/a		n/a
14	B	5/10/24	n/a	n/a	n/a		2
15	A	5/06/24		n/a	n/a	New A event added at end of table	n/a
16
17	B
Count Between

Cell Formulas
Range	Formula
E4:E15	E4	=IFERROR(MATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")
F4:F15	F4	=IFERROR(XMATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")
H4,H7:H13	H4	=IFNA(MATCH(B4,B5:B13,),"n/a")
H5	H5	=IFNA(MATCH(B5,B6:B$14,),"n/a")
H6	H6	=IFNA(MATCH(B6,B8:B16,),"n/a")
H14	H14	=IFNA(MATCH(B14,B16:B23,),"n/a")
H15	H15	=IFNA(MATCH(B15,B16:B23,),"n/a")

 

[JenniferMurphy]

In shaowu's solution, the end of the range that started out as "B12" increasesd in each sunsequent row. I thought I'd see what happens if I change that to B$12. That makes it even worse.

Can anyone explain why H7, highlighted in red, is "1"?

XMatch Function.xlsx
	B	C	D	E	F	G	H	I
3	Event	Date	Old Values	Match Solution	XMatch Solution	shaowu Solution1	shaowu Solution2	Comments
4	A	6/10/24	4	4	4	4	4
5	B	6/08/24	2	2	2	2	2
6	C	6/05/24	3	3	3	3	3
7	B	6/02/24	5	5	5	5	1	Why is this "1"?
8	A	5/30/24	n/a	n/a	n/a	n/a	n/a
9	C	5/25/24	n/a	n/a	n/a	n/a	n/a
10	D	5/20/24	1	1	1	1	1
11	D	5/19/24	n/a	n/a	n/a	n/a	1
12	B	5/10/24	n/a	n/a	n/a	2	1
13
14	B							Event text added outside table
Count Between

Cell Formulas
Range	Formula
E4:E12	E4	=IFERROR(MATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")
F4:F12	F4	=IFERROR(XMATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")
G4:G12	G4	=IFNA(MATCH(B4,B5:B12,),"n/a")
H4:H11	H4	=IFNA(MATCH(B4,B5:B$12),"n/a")
H12	H12	=IFNA(MATCH(B12,B$12:B13),"n/a")

 

[shaowu459]

In shaowu's solution, the end of the range that started out as "B12" increasesd in each sunsequent row. I thought I'd see what happens if I change that to B$12. That makes it even worse.

Can anyone explain why H7, highlighted in red, is "1"?

XMatch Function.xlsx
	B	C	D	E	F	G	H	I
3	Event	Date	Old Values	Match Solution	XMatch Solution	shaowu Solution1	shaowu Solution2	Comments
4	A	6/10/24	4	4	4	4	4
5	B	6/08/24	2	2	2	2	2
6	C	6/05/24	3	3	3	3	3
7	B	6/02/24	5	5	5	5	1	Why is this "1"?
8	A	5/30/24	n/a	n/a	n/a	n/a	n/a
9	C	5/25/24	n/a	n/a	n/a	n/a	n/a
10	D	5/20/24	1	1	1	1	1
11	D	5/19/24	n/a	n/a	n/a	n/a	1
12	B	5/10/24	n/a	n/a	n/a	2	1
13
14	B							Event text added outside table
Count Between

Cell Formulas
Range	Formula
E4:E12	E4	=IFERROR(MATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")
F4:F12	F4	=IFERROR(XMATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")
G4:G12	G4	=IFNA(MATCH(B4,B5:B12,),"n/a")
H4:H11	H4	=IFNA(MATCH(B4,B5:B$12),"n/a")
H12	H12	=IFNA(MATCH(B12,B$12:B13),"n/a")

you lost "," in MATCH.
H4=IFNA(MATCH(B4,B5:B$13,),"n/a")

 

[JenniferMurphy]

you lost "," in MATCH.
H4=IFNA(MATCH(B4,B5:B$13,),"n/a")

You are right about that. The default for the XMatch match_mode parameter is 0. I assumed it was the same for Match. I have corrected that, but it only solves part of the problem. I also added 2 new columns with XMatch replacing Match. They have similar problems. Errors highlighted in yellow.

XMatch Function.xlsx
	B	C	D	E	F	G	H	I	J	K
3	Event	Date	Old Values	Match Solution	(Best) XMatch Solution	Ralative Range w/Match	Absolute Range w/Match	Ralative Range w/XMatch	Absolute Range w/XMatch	Comments
4	A	6/10/24	4	4	4	4	4	4	4
5	B	6/08/24	2	2	2	2	2	2	2
6	C	6/05/24	3	3	3	3	3	3	3
7	B	6/02/24	5	5	5	5	5	5	5
8	A	5/30/24	n/a	n/a	n/a	6	n/a	6	n/a
9	C	5/25/24	n/a	n/a	n/a	9	n/a	n/a	n/a
10	D	5/20/24	1	1	1	1	1	1	1
11	D	5/19/24	n/a	n/a	n/a	9	n/a	n/a	n/a
12	B	5/10/24	n/a	n/a	n/a	3	1	4	1
13
14	A									Event text added outside table
15
16	B									Event text added outside table
17
18	C									Event text added outside table
19
20	D									Event text added outside table
Count Between

Cell Formulas
Range	Formula
E4:E12	E4	=IFERROR(MATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")
F4:F12	F4	=IFERROR(XMATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")
G4	G4	=IFNA(MATCH(B4,B5:B12,0),"n/a")
H4:H11	H4	=IFNA(MATCH(B4,B5:B$12,0),"n/a")
I4:I12	I4	=IFNA(XMATCH(B4,B5:B12,0),"n/a")
J4:J11	J4	=IFNA(XMATCH(B4,B5:B$12,0),"n/a")
G5	G5	=IFNA(MATCH(B5,B6:B14,0),"n/a")
G6:G8	G6	=IFNA(MATCH(B6,B7:B16,0),"n/a")
G9:G11	G9	=IFNA(MATCH(B9,B10:B20,0),"n/a")
G12	G12	=IFNA(MATCH(B12,B14:B23,0),"n/a")
H12	H12	=IFNA(MATCH(B12,B$12:B13,0),"n/a")
J12	J12	=IFNA(XMATCH(B12,B$12:B13,0),"n/a")

Then I added a few rows to the table. This caused even more problems for your solutions. Mine continue to work correctly.

XMatch Function.xlsx
	B	C	D	E	F	G	H	I	J	K
3	Event	Date	Old Values	(Good) Match Solution	(Best) XMatch Solution	(Fails) Ralative Range w/Match	(Fails) Absolute Range w/Match	(Fails) Ralative Range w/XMatch	(Fails) Absolute Range w/XMatch	Comments
4	D	6/11/24		8	8	8	8	8	8	New row 1
5	A	6/10/24	4	3	3	3	3	3	3
6	B	6/08/24	2	3	3	3	3	3	3
7	C	6/05/24	3	4	4	3	3	3	3
8	A	6/04/24		2	2	2	2	2	2	New row in niddle
9	B	6/02/24	5	5	5	5	5	5	5
10	A	5/30/24	n/a	5	5	5	n/a	5	n/a
11	C	5/25/24	n/a	n/a	n/a	10	n/a	n/a	n/a
12	D	5/20/24	1	1	1	1	1	1	1
13	D	5/19/24	n/a	n/a	n/a	10	n/a	n/a	n/a
14	B	5/10/24	n/a	n/a	n/a	3	1	4	1
15	A	5/05/24		n/a	n/a	2	3	2	3	New last row
16
17	A									Event text added outside table
18
19	B									Event text added outside table
20
21	C									Event text added outside table
22
23	D									Event text added outside table
Count Between

Cell Formulas
Range	Formula
E4:E15	E4	=IFERROR(MATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")
F4:F15	F4	=IFERROR(XMATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")
G4:G5,G15	G4	=IFNA(MATCH(B4,B5:B13,0),"n/a")
H4,H8	H4	=IFNA(MATCH(B4,B5:B$13,),"n/a")
I4:I5,I9:I13,I15	I4	=IFNA(XMATCH(B4,B5:B13,0),"n/a")
J4,J8	J4	=IFNA(XMATCH(B4,B5:B$13,0),"n/a")
H5:H6,H9:H13	H5	=IFNA(MATCH(B5,B6:B$14,0),"n/a")
J5:J6,J9:J13	J5	=IFNA(XMATCH(B5,B6:B$14,0),"n/a")
G6,G9:G10	G6	=IFNA(MATCH(B6,B7:B17,0),"n/a")
I6,I8	I6	=IFNA(XMATCH(B6,B7:B16,0),"n/a")
G7	G7	=IFNA(MATCH(B7,B9:B19,0),"n/a")
H7	H7	=IFNA(MATCH(B7,B9:B$14,0),"n/a")
I7	I7	=IFNA(XMATCH(B7,B9:B17,0),"n/a")
J7	J7	=IFNA(XMATCH(B7,B9:B$14,0),"n/a")
G8	G8	=IFNA(MATCH(B8,B9:B18,0),"n/a")
G11:G13	G11	=IFNA(MATCH(B11,B12:B23,0),"n/a")
G14	G14	=IFNA(MATCH(B14,B17:B26,0),"n/a")
H14	H14	=IFNA(MATCH(B14,B$14:B16,0),"n/a")
I14	I14	=IFNA(XMATCH(B14,B16:B23,0),"n/a")
J14	J14	=IFNA(XMATCH(B14,B$14:B16,0),"n/a")
H15	H15	=IFNA(MATCH(B15,B$13:B16,),"n/a")
J15	J15	=IFNA(XMATCH(B15,B$13:B16,0),"n/a")

I am curious as to why you mentioned my error on the match_mode paraneter, but have said nothing about the many errors with your solution. And even with the parameter error, you said nothing but the omission. Are you sure this is not something you got from an AI bot and you really don't understand it? You do know that the use of AI bots is prohibited here, right?

 

[shaowu459]

You are right about that. The default for the XMatch match_mode parameter is 0. I assumed it was the same for Match. I have corrected that, but it only solves part of the problem. I also added 2 new columns with XMatch replacing Match. They have similar problems. Errors highlighted in yellow.

XMatch Function.xlsx
	B	C	D	E	F	G	H	I	J	K
3	Event	Date	Old Values	Match Solution	(Best) XMatch Solution	Ralative Range w/Match	Absolute Range w/Match	Ralative Range w/XMatch	Absolute Range w/XMatch	Comments
4	A	6/10/24	4	4	4	4	4	4	4
5	B	6/08/24	2	2	2	2	2	2	2
6	C	6/05/24	3	3	3	3	3	3	3
7	B	6/02/24	5	5	5	5	5	5	5
8	A	5/30/24	n/a	n/a	n/a	6	n/a	6	n/a
9	C	5/25/24	n/a	n/a	n/a	9	n/a	n/a	n/a
10	D	5/20/24	1	1	1	1	1	1	1
11	D	5/19/24	n/a	n/a	n/a	9	n/a	n/a	n/a
12	B	5/10/24	n/a	n/a	n/a	3	1	4	1
13
14	A									Event text added outside table
15
16	B									Event text added outside table
17
18	C									Event text added outside table
19
20	D									Event text added outside table
Count Between

Cell Formulas
Range	Formula
E4:E12	E4	=IFERROR(MATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")
F4:F12	F4	=IFERROR(XMATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")
G4	G4	=IFNA(MATCH(B4,B5:B12,0),"n/a")
H4:H11	H4	=IFNA(MATCH(B4,B5:B$12,0),"n/a")
I4:I12	I4	=IFNA(XMATCH(B4,B5:B12,0),"n/a")
J4:J11	J4	=IFNA(XMATCH(B4,B5:B$12,0),"n/a")
G5	G5	=IFNA(MATCH(B5,B6:B14,0),"n/a")
G6:G8	G6	=IFNA(MATCH(B6,B7:B16,0),"n/a")
G9:G11	G9	=IFNA(MATCH(B9,B10:B20,0),"n/a")
G12	G12	=IFNA(MATCH(B12,B14:B23,0),"n/a")
H12	H12	=IFNA(MATCH(B12,B$12:B13,0),"n/a")
J12	J12	=IFNA(XMATCH(B12,B$12:B13,0),"n/a")

Then I added a few rows to the table. This caused even more problems for your solutions. Mine continue to work correctly.

XMatch Function.xlsx
	B	C	D	E	F	G	H	I	J	K
3	Event	Date	Old Values	(Good) Match Solution	(Best) XMatch Solution	(Fails) Ralative Range w/Match	(Fails) Absolute Range w/Match	(Fails) Ralative Range w/XMatch	(Fails) Absolute Range w/XMatch	Comments
4	D	6/11/24		8	8	8	8	8	8	New row 1
5	A	6/10/24	4	3	3	3	3	3	3
6	B	6/08/24	2	3	3	3	3	3	3
7	C	6/05/24	3	4	4	3	3	3	3
8	A	6/04/24		2	2	2	2	2	2	New row in niddle
9	B	6/02/24	5	5	5	5	5	5	5
10	A	5/30/24	n/a	5	5	5	n/a	5	n/a
11	C	5/25/24	n/a	n/a	n/a	10	n/a	n/a	n/a
12	D	5/20/24	1	1	1	1	1	1	1
13	D	5/19/24	n/a	n/a	n/a	10	n/a	n/a	n/a
14	B	5/10/24	n/a	n/a	n/a	3	1	4	1
15	A	5/05/24		n/a	n/a	2	3	2	3	New last row
16
17	A									Event text added outside table
18
19	B									Event text added outside table
20
21	C									Event text added outside table
22
23	D									Event text added outside table
Count Between

Cell Formulas
Range	Formula
E4:E15	E4	=IFERROR(MATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")
F4:F15	F4	=IFERROR(XMATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")
G4:G5,G15	G4	=IFNA(MATCH(B4,B5:B13,0),"n/a")
H4,H8	H4	=IFNA(MATCH(B4,B5:B$13,),"n/a")
I4:I5,I9:I13,I15	I4	=IFNA(XMATCH(B4,B5:B13,0),"n/a")
J4,J8	J4	=IFNA(XMATCH(B4,B5:B$13,0),"n/a")
H5:H6,H9:H13	H5	=IFNA(MATCH(B5,B6:B$14,0),"n/a")
J5:J6,J9:J13	J5	=IFNA(XMATCH(B5,B6:B$14,0),"n/a")
G6,G9:G10	G6	=IFNA(MATCH(B6,B7:B17,0),"n/a")
I6,I8	I6	=IFNA(XMATCH(B6,B7:B16,0),"n/a")
G7	G7	=IFNA(MATCH(B7,B9:B19,0),"n/a")
H7	H7	=IFNA(MATCH(B7,B9:B$14,0),"n/a")
I7	I7	=IFNA(XMATCH(B7,B9:B17,0),"n/a")
J7	J7	=IFNA(XMATCH(B7,B9:B$14,0),"n/a")
G8	G8	=IFNA(MATCH(B8,B9:B18,0),"n/a")
G11:G13	G11	=IFNA(MATCH(B11,B12:B23,0),"n/a")
G14	G14	=IFNA(MATCH(B14,B17:B26,0),"n/a")
H14	H14	=IFNA(MATCH(B14,B$14:B16,0),"n/a")
I14	I14	=IFNA(XMATCH(B14,B16:B23,0),"n/a")
J14	J14	=IFNA(XMATCH(B14,B$14:B16,0),"n/a")
H15	H15	=IFNA(MATCH(B15,B$13:B16,),"n/a")
J15	J15	=IFNA(XMATCH(B15,B$13:B16,0),"n/a")

I am curious as to why you mentioned my error on the match_mode paraneter, but have said nothing about the many errors with your solution. And even with the parameter error, you said nothing but the omission. Are you sure this is not something you got from an AI bot and you really don't understand it? You do know that the use of AI bots is prohibited here, right?

please refer to my post #6, the formula is:
H4=IFNA(MATCH(B4,B5:B$13,),"n/a")
copy and paste above formula in H4.

 

[shaowu459]

You are right about that. The default for the XMatch match_mode parameter is 0. I assumed it was the same for Match. I have corrected that, but it only solves part of the problem. I also added 2 new columns with XMatch replacing Match. They have similar problems. Errors highlighted in yellow.

XMatch Function.xlsx
	B	C	D	E	F	G	H	I	J	K
3	Event	Date	Old Values	Match Solution	(Best) XMatch Solution	Ralative Range w/Match	Absolute Range w/Match	Ralative Range w/XMatch	Absolute Range w/XMatch	Comments
4	A	6/10/24	4	4	4	4	4	4	4
5	B	6/08/24	2	2	2	2	2	2	2
6	C	6/05/24	3	3	3	3	3	3	3
7	B	6/02/24	5	5	5	5	5	5	5
8	A	5/30/24	n/a	n/a	n/a	6	n/a	6	n/a
9	C	5/25/24	n/a	n/a	n/a	9	n/a	n/a	n/a
10	D	5/20/24	1	1	1	1	1	1	1
11	D	5/19/24	n/a	n/a	n/a	9	n/a	n/a	n/a
12	B	5/10/24	n/a	n/a	n/a	3	1	4	1
13
14	A									Event text added outside table
15
16	B									Event text added outside table
17
18	C									Event text added outside table
19
20	D									Event text added outside table
Count Between

Cell Formulas
Range	Formula
E4:E12	E4	=IFERROR(MATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")
F4:F12	F4	=IFERROR(XMATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")
G4	G4	=IFNA(MATCH(B4,B5:B12,0),"n/a")
H4:H11	H4	=IFNA(MATCH(B4,B5:B$12,0),"n/a")
I4:I12	I4	=IFNA(XMATCH(B4,B5:B12,0),"n/a")
J4:J11	J4	=IFNA(XMATCH(B4,B5:B$12,0),"n/a")
G5	G5	=IFNA(MATCH(B5,B6:B14,0),"n/a")
G6:G8	G6	=IFNA(MATCH(B6,B7:B16,0),"n/a")
G9:G11	G9	=IFNA(MATCH(B9,B10:B20,0),"n/a")
G12	G12	=IFNA(MATCH(B12,B14:B23,0),"n/a")
H12	H12	=IFNA(MATCH(B12,B$12:B13,0),"n/a")
J12	J12	=IFNA(XMATCH(B12,B$12:B13,0),"n/a")

Then I added a few rows to the table. This caused even more problems for your solutions. Mine continue to work correctly.

XMatch Function.xlsx
	B	C	D	E	F	G	H	I	J	K
3	Event	Date	Old Values	(Good) Match Solution	(Best) XMatch Solution	(Fails) Ralative Range w/Match	(Fails) Absolute Range w/Match	(Fails) Ralative Range w/XMatch	(Fails) Absolute Range w/XMatch	Comments
4	D	6/11/24		8	8	8	8	8	8	New row 1
5	A	6/10/24	4	3	3	3	3	3	3
6	B	6/08/24	2	3	3	3	3	3	3
7	C	6/05/24	3	4	4	3	3	3	3
8	A	6/04/24		2	2	2	2	2	2	New row in niddle
9	B	6/02/24	5	5	5	5	5	5	5
10	A	5/30/24	n/a	5	5	5	n/a	5	n/a
11	C	5/25/24	n/a	n/a	n/a	10	n/a	n/a	n/a
12	D	5/20/24	1	1	1	1	1	1	1
13	D	5/19/24	n/a	n/a	n/a	10	n/a	n/a	n/a
14	B	5/10/24	n/a	n/a	n/a	3	1	4	1
15	A	5/05/24		n/a	n/a	2	3	2	3	New last row
16
17	A									Event text added outside table
18
19	B									Event text added outside table
20
21	C									Event text added outside table
22
23	D									Event text added outside table
Count Between

Cell Formulas
Range	Formula
E4:E15	E4	=IFERROR(MATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")
F4:F15	F4	=IFERROR(XMATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")
G4:G5,G15	G4	=IFNA(MATCH(B4,B5:B13,0),"n/a")
H4,H8	H4	=IFNA(MATCH(B4,B5:B$13,),"n/a")
I4:I5,I9:I13,I15	I4	=IFNA(XMATCH(B4,B5:B13,0),"n/a")
J4,J8	J4	=IFNA(XMATCH(B4,B5:B$13,0),"n/a")
H5:H6,H9:H13	H5	=IFNA(MATCH(B5,B6:B$14,0),"n/a")
J5:J6,J9:J13	J5	=IFNA(XMATCH(B5,B6:B$14,0),"n/a")
G6,G9:G10	G6	=IFNA(MATCH(B6,B7:B17,0),"n/a")
I6,I8	I6	=IFNA(XMATCH(B6,B7:B16,0),"n/a")
G7	G7	=IFNA(MATCH(B7,B9:B19,0),"n/a")
H7	H7	=IFNA(MATCH(B7,B9:B$14,0),"n/a")
I7	I7	=IFNA(XMATCH(B7,B9:B17,0),"n/a")
J7	J7	=IFNA(XMATCH(B7,B9:B$14,0),"n/a")
G8	G8	=IFNA(MATCH(B8,B9:B18,0),"n/a")
G11:G13	G11	=IFNA(MATCH(B11,B12:B23,0),"n/a")
G14	G14	=IFNA(MATCH(B14,B17:B26,0),"n/a")
H14	H14	=IFNA(MATCH(B14,B$14:B16,0),"n/a")
I14	I14	=IFNA(XMATCH(B14,B16:B23,0),"n/a")
J14	J14	=IFNA(XMATCH(B14,B$14:B16,0),"n/a")
H15	H15	=IFNA(MATCH(B15,B$13:B16,),"n/a")
J15	J15	=IFNA(XMATCH(B15,B$13:B16,0),"n/a")

I am curious as to why you mentioned my error on the match_mode paraneter, but have said nothing about the many errors with your solution. And even with the parameter error, you said nothing but the omission. Are you sure this is not something you got from an AI bot and you really don't understand it? You do know that the use of AI bots is prohibited here, right?

i am not an english speaker, it is hard for me to explain every thing in english. i made the formula by my self, your issue is not a difficult one, just a common match thing. if you have a translate machine, i can explain your questions in detail.

 

[shaowu459]

You are right about that. The default for the XMatch match_mode parameter is 0. I assumed it was the same for Match. I have corrected that, but it only solves part of the problem. I also added 2 new columns with XMatch replacing Match. They have similar problems. Errors highlighted in yellow.

XMatch Function.xlsx
	B	C	D	E	F	G	H	I	J	K
3	Event	Date	Old Values	Match Solution	(Best) XMatch Solution	Ralative Range w/Match	Absolute Range w/Match	Ralative Range w/XMatch	Absolute Range w/XMatch	Comments
4	A	6/10/24	4	4	4	4	4	4	4
5	B	6/08/24	2	2	2	2	2	2	2
6	C	6/05/24	3	3	3	3	3	3	3
7	B	6/02/24	5	5	5	5	5	5	5
8	A	5/30/24	n/a	n/a	n/a	6	n/a	6	n/a
9	C	5/25/24	n/a	n/a	n/a	9	n/a	n/a	n/a
10	D	5/20/24	1	1	1	1	1	1	1
11	D	5/19/24	n/a	n/a	n/a	9	n/a	n/a	n/a
12	B	5/10/24	n/a	n/a	n/a	3	1	4	1
13
14	A									Event text added outside table
15
16	B									Event text added outside table
17
18	C									Event text added outside table
19
20	D									Event text added outside table
Count Between

Cell Formulas
Range	Formula
E4:E12	E4	=IFERROR(MATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")
F4:F12	F4	=IFERROR(XMATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")
G4	G4	=IFNA(MATCH(B4,B5:B12,0),"n/a")
H4:H11	H4	=IFNA(MATCH(B4,B5:B$12,0),"n/a")
I4:I12	I4	=IFNA(XMATCH(B4,B5:B12,0),"n/a")
J4:J11	J4	=IFNA(XMATCH(B4,B5:B$12,0),"n/a")
G5	G5	=IFNA(MATCH(B5,B6:B14,0),"n/a")
G6:G8	G6	=IFNA(MATCH(B6,B7:B16,0),"n/a")
G9:G11	G9	=IFNA(MATCH(B9,B10:B20,0),"n/a")
G12	G12	=IFNA(MATCH(B12,B14:B23,0),"n/a")
H12	H12	=IFNA(MATCH(B12,B$12:B13,0),"n/a")
J12	J12	=IFNA(XMATCH(B12,B$12:B13,0),"n/a")

Then I added a few rows to the table. This caused even more problems for your solutions. Mine continue to work correctly.

XMatch Function.xlsx
	B	C	D	E	F	G	H	I	J	K
3	Event	Date	Old Values	(Good) Match Solution	(Best) XMatch Solution	(Fails) Ralative Range w/Match	(Fails) Absolute Range w/Match	(Fails) Ralative Range w/XMatch	(Fails) Absolute Range w/XMatch	Comments
4	D	6/11/24		8	8	8	8	8	8	New row 1
5	A	6/10/24	4	3	3	3	3	3	3
6	B	6/08/24	2	3	3	3	3	3	3
7	C	6/05/24	3	4	4	3	3	3	3
8	A	6/04/24		2	2	2	2	2	2	New row in niddle
9	B	6/02/24	5	5	5	5	5	5	5
10	A	5/30/24	n/a	5	5	5	n/a	5	n/a
11	C	5/25/24	n/a	n/a	n/a	10	n/a	n/a	n/a
12	D	5/20/24	1	1	1	1	1	1	1
13	D	5/19/24	n/a	n/a	n/a	10	n/a	n/a	n/a
14	B	5/10/24	n/a	n/a	n/a	3	1	4	1
15	A	5/05/24		n/a	n/a	2	3	2	3	New last row
16
17	A									Event text added outside table
18
19	B									Event text added outside table
20
21	C									Event text added outside table
22
23	D									Event text added outside table
Count Between

Cell Formulas
Range	Formula
E4:E15	E4	=IFERROR(MATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")
F4:F15	F4	=IFERROR(XMATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")
G4:G5,G15	G4	=IFNA(MATCH(B4,B5:B13,0),"n/a")
H4,H8	H4	=IFNA(MATCH(B4,B5:B$13,),"n/a")
I4:I5,I9:I13,I15	I4	=IFNA(XMATCH(B4,B5:B13,0),"n/a")
J4,J8	J4	=IFNA(XMATCH(B4,B5:B$13,0),"n/a")
H5:H6,H9:H13	H5	=IFNA(MATCH(B5,B6:B$14,0),"n/a")
J5:J6,J9:J13	J5	=IFNA(XMATCH(B5,B6:B$14,0),"n/a")
G6,G9:G10	G6	=IFNA(MATCH(B6,B7:B17,0),"n/a")
I6,I8	I6	=IFNA(XMATCH(B6,B7:B16,0),"n/a")
G7	G7	=IFNA(MATCH(B7,B9:B19,0),"n/a")
H7	H7	=IFNA(MATCH(B7,B9:B$14,0),"n/a")
I7	I7	=IFNA(XMATCH(B7,B9:B17,0),"n/a")
J7	J7	=IFNA(XMATCH(B7,B9:B$14,0),"n/a")
G8	G8	=IFNA(MATCH(B8,B9:B18,0),"n/a")
G11:G13	G11	=IFNA(MATCH(B11,B12:B23,0),"n/a")
G14	G14	=IFNA(MATCH(B14,B17:B26,0),"n/a")
H14	H14	=IFNA(MATCH(B14,B$14:B16,0),"n/a")
I14	I14	=IFNA(XMATCH(B14,B16:B23,0),"n/a")
J14	J14	=IFNA(XMATCH(B14,B$14:B16,0),"n/a")
H15	H15	=IFNA(MATCH(B15,B$13:B16,),"n/a")
J15	J15	=IFNA(XMATCH(B15,B$13:B16,0),"n/a")

I am curious as to why you mentioned my error on the match_mode paraneter, but have said nothing about the many errors with your solution. And even with the parameter error, you said nothing but the omission. Are you sure this is not something you got from an AI bot and you really don't understand it? You do know that the use of AI bots is prohibited here, right?

further more, you can see my other post, i don't think ai could generate such proper solutions.