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

#### Jasesair

##### Active Member
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!

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### mrshl9898

##### Well-known Member
maybe =COUNTIFS(\$A\$2:\$A2,\$BI\$11,\$C\$2:\$C2,\$BJ\$11,\$D\$2:\$D2,\$BK\$11,\$G\$2:\$G2,\$BL\$11)

#### Jasesair

##### Active Member
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-known Member
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

##### Active Member

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

Book2
ABCDEFGHIJKL
1NameVenueYearTermTrue/False
2JohnLondon20211TRUE
3JohnLondon20212TRUE
4JohnLondon20213FALSE
5JohnLondon20214TRUE
6JohnLondon20221TRUE
7JohnLondon20222TRUE
8JohnLondon20223TRUE
9JohnLondon20224TRUE
10JohnLondon20231FALSECriteria:
11JohnLondon20232TRUEJohn2022Term 1TRUE
12JohnLondon20233FALSE
13JohnLondon20234TRUE
14JohnNew York20211TRUE
15JohnNew York20212TRUEResult:
16JohnNew York20213FALSE
17JohnNew York20214TRUE
18JohnNew York20221FALSE
19JohnNew York20222FALSE
20JohnNew York20223TRUE
21JohnNew York20224TRUE
22JohnNew York20231FALSE
23JohnNew York20232TRUE
24JohnNew York20233FALSE
25JohnNew York20234FALSE
26JohnSydney20211TRUE
27JohnSydney20212TRUE
28JohnSydney20213FALSE
29JohnSydney20214TRUE
30JohnSydney20221FALSE
31JohnSydney20222TRUE
32JohnSydney20223TRUE
33JohnSydney20224TRUE
34JohnSydney20231FALSE
35JohnSydney20232TRUE
36JohnSydney20233FALSE
37JohnSydney20234FALSE
38JohnBerlin20211TRUE
39JohnBerlin20212TRUE
40JohnBerlin20213FALSE
41JohnBerlin20214TRUE
42JohnBerlin20221TRUE
43JohnBerlin20222FALSE
44JohnBerlin20223TRUE
45JohnBerlin20224TRUE
46JohnBerlin20231FALSE
47JohnBerlin20232TRUE
48JohnBerlin20233FALSE
49JohnBerlin20234FALSE
Sheet1

#### mrshl9898

##### Well-known Member
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

##### Active Member

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.

#### Jasesair

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

Last edited:

Replies
8
Views
80
Replies
3
Views
240
Replies
5
Views
152
Replies
1
Views
49
Replies
3
Views
114

1,136,798
Messages
5,677,801
Members
419,720
Latest member
kurman

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back