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

Jasesair

Active Member
Joined
Apr 8, 2015
Messages
253
Office Version
  1. 2016
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
Joined
Feb 6, 2012
Messages
1,951
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
Joined
Apr 8, 2015
Messages
253
Office Version
  1. 2016
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
Joined
Feb 6, 2012
Messages
1,951
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
Joined
Apr 8, 2015
Messages
253
Office Version
  1. 2016

ADVERTISEMENT

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
Joined
Feb 6, 2012
Messages
1,951
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),"")

NameVenueYearTermcountTrue/False
JohnLondon202110TRUE
JohnLondon202120TRUE
JohnLondon202130FALSE
JohnLondon202140TRUE
JohnLondon202211TRUE
JohnLondon202221TRUE
JohnLondon202231TRUE
JohnLondon202241TRUE
JohnLondon202311FALSECriteria:
JohnLondon202321TRUEJohn2022Term 1TRUE
JohnLondon202331FALSE
JohnLondon202341TRUE
JohnNew York202111TRUE
JohnNew York202121TRUEResult:
JohnNew York202131FALSELondon
JohnNew York202141TRUEBerlin
JohnNew York202211FALSE
JohnNew York202221FALSE
JohnNew York202231TRUE
JohnNew York202241TRUE
JohnNew York202311FALSE
JohnNew York202321TRUE
JohnNew York202331FALSE
JohnNew York202341FALSE
JohnSydney202111TRUE
JohnSydney202121TRUE
JohnSydney202131FALSE
JohnSydney202141TRUE
JohnSydney202211FALSE
JohnSydney202221TRUE
JohnSydney202231TRUE
JohnSydney202241TRUE
JohnSydney202311FALSE
JohnSydney202321TRUE
JohnSydney202331FALSE
JohnSydney202341FALSE
JohnBerlin202111TRUE
JohnBerlin202121TRUE
JohnBerlin202131FALSE
JohnBerlin202141TRUE
JohnBerlin202212TRUE
JohnBerlin202222FALSE
JohnBerlin202232TRUE
JohnBerlin202242TRUE
JohnBerlin202312FALSE
JohnBerlin202322TRUE
JohnBerlin202332FALSE
JohnBerlin202342FALSE
 

Jasesair

Active Member
Joined
Apr 8, 2015
Messages
253
Office Version
  1. 2016

ADVERTISEMENT

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
Joined
Apr 8, 2015
Messages
253
Office Version
  1. 2016
All good, think I've worked it out. Thanks again for your time.
 
Last edited:

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top