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)),""),"")

 

[ste33uka]

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)

strange when i use that formula i get #name?

Book2.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1						red				#NAME?	#NAME?
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
19
Sheet1

Cell Formulas
Range	Formula
J1	J1	=CHOOSECOLS(SORT(FILTER(HSTACK(B4:B11,SEQUENCE(COUNTA(B4:B11))),F1&F2=C4:C11&D4:D11),2,-1),1)
K1	K1	=CHOOSECOLS(SORT(FILTER(HSTACK(B4:B11,SEQUENCE(COUNTA(B4:B11))),(C4:C11=F1)*(D4:D11=F2)),2,-1),1)
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)),""),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Alex Blakenburg]

I think ChooseCols might be one of the later functions, if you type =ChooseCols does Excel recognise it ?
If it does how about =HStack ?
You are definitely using MS 365 right ?

 

[ste33uka]

I think ChooseCols might be one of the later functions, if you type =ChooseCols does Excel recognise it ?
If it does how about =HStack ?
You are definitely using MS 365 right ?

Book2.xlsx
	A	B	C	D	E	F
1
2
3
4	#NAME?
5	#NAME?
6
7
8
9
10
11
12
13
14
15
16
Sheet2

Cell Formulas
Range	Formula
A4	A4	=CHOOSECOLS
A5	A5	=HSTACK

Says version 2203 build 15028.20228

 

[Alex Blakenburg]

If you have a personal subscription you might want to update your software.
If you are in an organisation, that is a long way behind for even the biannual cycle that a lot of companies seem to be on and you might want to ask the question.
I think HStack and ChooseCols have been around since Mar 2022 and the release note list on the below only goes back to vrs 2206 Jul 2022

Release notes for Current Channel releases - Office release notes

Provides IT Pros with release notes for Monthly Channel releases for Microsoft 365 Apps.

learn.microsoft.com

The ChooseCols can be replaced with the Index function, not sure how to get around the HStack function.

 

[ste33uka]

If you have a personal subscription you might want to update your software.
If you are in an organisation, that is a long way behind for even the biannual cycle that a lot of companies seem to be on and you might want to ask the question.
I think HStack and ChooseCols have been around since Mar 2022 and the release note list on the below only goes back to vrs 2206 Jul 2022

Release notes for Current Channel releases - Office release notes

Provides IT Pros with release notes for Monthly Channel releases for Microsoft 365 Apps.

learn.microsoft.com

The ChooseCols can be replaced with the Index function, not sure how to get around the HStack function.

im not in an organisation
Thanks for help, ill have to look into it

 

[Fluff]

How about

=LET(f,FILTER($AY$204296:$AY$525555,($AW$204296:$AW$525555=F5)*($AA$204296:$AA$525555=F6)),INDEX(f,SEQUENCE(4,,ROWS(f),-1)))

 

[ste33uka]

How about

=LET(f,FILTER($AY$204296:$AY$525555,($AW$204296:$AW$525555=F5)*($AA$204296:$AA$525555=F6)),INDEX(f,SEQUENCE(4,,ROWS(f),-1)))

Hi fluff, just returns #calc!
edit thanks alot fluff that works, is there anyway that result would go accross the row instead of down ?

 

[Fluff]

How about

=LET(f,FILTER($AY$204296:$AY$525555,($AW$204296:$AW$525555=F5)*($AA$204296:$AA$525555=F6)),INDEX(f,SEQUENCE(MIN(4,ROWS(f)),,ROWS(f),-1)))

 

[ste33uka]

How about

=LET(f,FILTER($AY$204296:$AY$525555,($AW$204296:$AW$525555=F5)*($AA$204296:$AA$525555=F6)),INDEX(f,SEQUENCE(MIN(4,ROWS(f)),,ROWS(f),-1)))

is there anyway that result would go accross the row instead of down ?
edit
sorted

=TRANSPOSE(LET(f,FILTER($B$1:$B$22,($C$1:$C$22=F1)*($D$1:$D$22=F2)),INDEX(f,SEQUENCE(5,,ROWS(f),-1))))

Thanks alot fluff for your help

 

[Fluff]

Glad we could help & thanks for the feedback.