Faster formula than this ?

[ste33uka]

I have hundreds of formulas like this per sheet that finds 1st,2nd,3rd,4th last occurance, is there any faster formulas ?
Thanks

=IF(F4<>"",IFERROR(INDEX($AY$204296:$AY$525555,AGGREGATE(14,6,ROW($D$1:$D$525555)/($AW$204296:$AW$525555=F5)/($AA$204296:$AA$525555=F6),1)),""),"")

=IF(F4<>"",IFERROR(INDEX($AY$204296:$AY$525555,AGGREGATE(14,6,ROW($D$1:$D$525555)/($AW$204296:$AW$525555=F5)/($AA$204296:$AA$525555=F6),2)),""),"")

=IF(F4<>"",IFERROR(INDEX($AY$204296:$AY$525555,AGGREGATE(14,6,ROW($D$1:$D$525555)/($AW$204296:$AW$525555=F5)/($AA$204296:$AA$525555=F6),3)),""),"")

=IF(F4<>"",IFERROR(INDEX($AY$204296:$AY$525555,AGGREGATE(14,6,ROW($D$1:$D$525555)/($AW$204296:$AW$525555=F5)/($AA$204296:$AA$525555=F6),4)),""),"")

 

[Skybluekid]

You may want to try the below:

Book1
	B	C	D	E	F	G	H	I	J
1
2	Criteria 1	Criteria 2
3	a	b	1	a	c			7
4			2	b	b			5
5			3	a	b			3
6			4	c	b
7			5	a	b
8			6	a	c
9			7	a	b
10
Sheet1

Cell Formulas
Range	Formula
I3:I5	I3	=SORT(FILTER(D3:D9,B3&C3=E3:E9&F3:F9),,-1)
Dynamic array formulas.

 

[ste33uka]

Thanks never knew about dynamic array formulas, have tested
=SORT(FILTER(D3:D9,"a"&"b"=E3:E9&F3:F9),,-1)
but it sorts by max number , instead of last to 1st occurance

 

[Skybluekid]

Change to

=SORT(FILTER(D3:D9,"a"&"b"=E3:E9&F3:F9),,1)

 

[ste33uka]

tried that , that gets results min to max, i am trring to get last occurance to 1st occurance , so it searches column from last row to 1st row.

 

[Skybluekid]

Are you able to post some sample data?

In your original formula, you are using the Large Function within Aggregate? So would not that get the 1st, 2nd etc, largest occurance rather that the smallest? So my original solution looks at the array and returns the greatest number, ie 7 which is that last occurance and then put that at the top of the list. Excel will alway search from top to bottom, it is the function that will manipulate the search to show the result ie XMATCH has a parameter that to switch the search from the bottom of the list.

Book1
	A	B	C	D	E	F	G	H
2		Criteria 1	Criteria 2
3
4		a	b	1	a	c		7
5				2	b	b
6				3	a	b
7				4	c	b
8				5	a	b
9				6	a	c
10				7	a	b
Sheet2

Cell Formulas
Range	Formula
H4	H4	=XMATCH(B4&C4,E4:E10&F4:F10,0,-1)

So the Sort Function shows the largest (your 1st Large value) at the top.

 

[ste33uka]

sorry cant post sample data , but my original formulas definatly get last occurance to 1st occurance

this gets last occurance =IF(F4<>"",IFERROR(INDEX($AY$204296:$AY$525555,AGGREGATE(14,6,ROW($D$1:$D$525555)/($AW$204296:$AW$525555=F5)/($AA$204296:$AA$525555=F6),1)),""),"")

this gets second last occurance=IF(F4<>"",IFERROR(INDEX($AY$204296:$AY$525555,AGGREGATE(14,6,ROW($D$1:$D$525555)/($AW$204296:$AW$525555=F5)/($AA$204296:$AA$525555=F6),2)),""),"")

 

[Alex Blakenburg]

Without an XL2BB am not really following what you are doing but leveraging off @Skybluekid's post #2 and homing in your terminology of Last to First occurence, would this work for you ?

=CHOOSECOLS(SORT(FILTER(HSTACK(D3:D9,SEQUENCE(COUNTA(D3:D9))),B3&C3=E3:E9&F3:F9),2,-1),1)

 

[ste33uka]

Book2.xlsx
	A	B	C	D	E	F
1						red
2						blue
3
4	6	7	red	blue
5	9	9	red	blue
6	7	12
7		2
8		6	red	blue
9		7
10		8
11		18
12
13
14
15
16
17
18
Sheet1

Cell Formulas
Range	Formula
A4	A4	=IF(F1<>"",IFERROR(INDEX($B$1:$B$20,AGGREGATE(14,6,ROW($B$1:$B$20)/($C$1:$C$20=F1)/($D$1:$D$20=F2),1)),""),"")
A5	A5	=IF(F1<>"",IFERROR(INDEX($B$1:$B$20,AGGREGATE(14,6,ROW($B$1:$B$20)/($C$1:$C$20=F1)/($D$1:$D$20=F2),2)),""),"")
A6	A6	=IF(F1<>"",IFERROR(INDEX($B$1:$B$20,AGGREGATE(14,6,ROW($B$1:$B$20)/($C$1:$C$20=F1)/($D$1:$D$20=F2),3)),""),"")

 

[Alex Blakenburg]

Just reconfiguring post #8 to fit your data seems to work for me:

=CHOOSECOLS(SORT(FILTER(HSTACK(B4:B11,SEQUENCE(COUNTA(B4:B11))),F1&F2=C4:C11&D4:D11),2,-1),1)

Or using the multiply rather than concatenate method:

=CHOOSECOLS(SORT(FILTER(HSTACK(B4:B11,SEQUENCE(COUNTA(B4:B11))),(C4:C11=F1)*(D4:D11=F2)),2,-1),1)