The month column contains numbers form 1 to 12.

The formula I wrote in F2 works well when month is 1 because the selected ranges are for month 1 only.

I want to make it dynamic such that it includes all months.

Here the cell range $B$2:$B$100,$A$2:$A$100 contains data for only month 1.

In this formula I did XLOOKUP to find the last occurrence of respective user id(column B), which ultimately fetches me the instance id(column A). Then I marked the last occurred instance id for that respective user as 1.

CodePudding user response：

Try IF() WITH COUNTIFS()

=IF(COUNTIFS(A:A,">" & $A2,B:B,$B2,C:C,$C2)>0,0,1)