Return 1st, 2nd, 3rd occurrence of multiple criteria being met

[Jasesair]

Really not sure where to start with this one. I have multiple criteria that need to be met, and if met, I want a formula that's going to return the corresponding data in B:B of the first instance of that being correct. I'd then like the formula to be able to be dragged down to produce the second, third, fourth etc occurrence. The criteria are below in a previously used countifs formula.

COUNTIFS($A:$A,$BI$11,$C:$C,$BJ$11,$D:$D,$BK$11,G:$G,$BL11)

Hoping someone can assist. Big thanks!

 

[mrshl9898]

maybe =COUNTIFS($A$2:$A2,$BI$11,$C$2:$C2,$BJ$11,$D$2:$D2,$BK$11,$G$2:$G2,$BL$11)

 

[Jasesair]

I don't think it'll be a count function. Perhaps an index match but will need to match multiple criteria. My big unresolved issue is how the formula will be able to return the first occurrence, and then be able to be dragged down to list additional occurrences.

 

[mrshl9898]

Well without seeing what the data or results look like I'd assume that would place a 1,2 etc next to each occurrence

 

[Jasesair]

Hoping this works. I've mocked something up. In this case with the selected criteria, the results should be:
I16 = London
I17 = Berlin

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Venue	Year	Term			True/False
2	John	London	2021	1			TRUE
3	John	London	2021	2			TRUE
4	John	London	2021	3			FALSE
5	John	London	2021	4			TRUE
6	John	London	2022	1			TRUE
7	John	London	2022	2			TRUE
8	John	London	2022	3			TRUE
9	John	London	2022	4			TRUE
10	John	London	2023	1			FALSE		Criteria:
11	John	London	2023	2			TRUE		John	2022	Term 1	TRUE
12	John	London	2023	3			FALSE
13	John	London	2023	4			TRUE
14	John	New York	2021	1			TRUE
15	John	New York	2021	2			TRUE		Result:
16	John	New York	2021	3			FALSE
17	John	New York	2021	4			TRUE
18	John	New York	2022	1			FALSE
19	John	New York	2022	2			FALSE
20	John	New York	2022	3			TRUE
21	John	New York	2022	4			TRUE
22	John	New York	2023	1			FALSE
23	John	New York	2023	2			TRUE
24	John	New York	2023	3			FALSE
25	John	New York	2023	4			FALSE
26	John	Sydney	2021	1			TRUE
27	John	Sydney	2021	2			TRUE
28	John	Sydney	2021	3			FALSE
29	John	Sydney	2021	4			TRUE
30	John	Sydney	2022	1			FALSE
31	John	Sydney	2022	2			TRUE
32	John	Sydney	2022	3			TRUE
33	John	Sydney	2022	4			TRUE
34	John	Sydney	2023	1			FALSE
35	John	Sydney	2023	2			TRUE
36	John	Sydney	2023	3			FALSE
37	John	Sydney	2023	4			FALSE
38	John	Berlin	2021	1			TRUE
39	John	Berlin	2021	2			TRUE
40	John	Berlin	2021	3			FALSE
41	John	Berlin	2021	4			TRUE
42	John	Berlin	2022	1			TRUE
43	John	Berlin	2022	2			FALSE
44	John	Berlin	2022	3			TRUE
45	John	Berlin	2022	4			TRUE
46	John	Berlin	2023	1			FALSE
47	John	Berlin	2023	2			TRUE
48	John	Berlin	2023	3			FALSE
49	John	Berlin	2023	4			FALSE
Sheet1

 

[mrshl9898]

With a helper column:

E2, dragged down:=COUNTIFS($A$2:$A2,$I$11,$C$2:$C2,$J$11,$D$2:$D2,RIGHT($K$11,1),$G$2:$G2,$L$11)
I16, dragged down:=IFERROR(INDEX(B:B,MATCH(ROW(A1),E:E,0),1),"")

Name	Venue	Year	Term	count		True/False
John	London	2021	1	0		TRUE
John	London	2021	2	0		TRUE
John	London	2021	3	0		FALSE
John	London	2021	4	0		TRUE
John	London	2022	1	1		TRUE
John	London	2022	2	1		TRUE
John	London	2022	3	1		TRUE
John	London	2022	4	1		TRUE
John	London	2023	1	1		FALSE		Criteria:
John	London	2023	2	1		TRUE		John	2022	Term 1	TRUE
John	London	2023	3	1		FALSE
John	London	2023	4	1		TRUE
John	New York	2021	1	1		TRUE
John	New York	2021	2	1		TRUE		Result:
John	New York	2021	3	1		FALSE		London
John	New York	2021	4	1		TRUE		Berlin
John	New York	2022	1	1		FALSE
John	New York	2022	2	1		FALSE
John	New York	2022	3	1		TRUE
John	New York	2022	4	1		TRUE
John	New York	2023	1	1		FALSE
John	New York	2023	2	1		TRUE
John	New York	2023	3	1		FALSE
John	New York	2023	4	1		FALSE
John	Sydney	2021	1	1		TRUE
John	Sydney	2021	2	1		TRUE
John	Sydney	2021	3	1		FALSE
John	Sydney	2021	4	1		TRUE
John	Sydney	2022	1	1		FALSE
John	Sydney	2022	2	1		TRUE
John	Sydney	2022	3	1		TRUE
John	Sydney	2022	4	1		TRUE
John	Sydney	2023	1	1		FALSE
John	Sydney	2023	2	1		TRUE
John	Sydney	2023	3	1		FALSE
John	Sydney	2023	4	1		FALSE
John	Berlin	2021	1	1		TRUE
John	Berlin	2021	2	1		TRUE
John	Berlin	2021	3	1		FALSE
John	Berlin	2021	4	1		TRUE
John	Berlin	2022	1	2		TRUE
John	Berlin	2022	2	2		FALSE
John	Berlin	2022	3	2		TRUE
John	Berlin	2022	4	2		TRUE
John	Berlin	2023	1	2		FALSE
John	Berlin	2023	2	2		TRUE
John	Berlin	2023	3	2		FALSE
John	Berlin	2023	4	2		FALSE

 

[Jasesair]

Awesome. Any chance you could show me how to amend if column G isn't needed. I had that in there as a helper column myself, but that's clearly not needed. I'll put the Count Column in column G instead. L11 obviously isn't needed anymore either.

 

[mrshl9898]

I would if I knew, hopefully someone else can chime in with that.

Was looking into this Excel formula: Get nth match with INDEX / MATCH | Exceljet but not sure how to adapt it for multiple searches

 

[Jasesair]

All good, think I've worked it out. Thanks again for your time.