Picking out 2nd, 3rd etc highest values

[Corleone]

Hi

Im currently using this formula below to pick out the 1st/2nd/3rd/4th etc latest Activity dates when a criteria is met in the source data (changing the 3 at the end as required)

The Match is in Column A and the dates are being picked up from column C (The example below is looking for matches to Activity 1)
The results are then placed in column F (rows 2 down to 11) as it is looking for the latest 10 dates

{=INDEX($C$2:$C$11,SMALL(IF($F$1=$A$2:$A$11,ROW($A$2:$A$11)-ROW($A$2)+1),3)}

How would i get the corresponding activity description from column B to appear in column E.
would i need to create some form of an offset command based on the above formula?

Im using Excel Office 365

Thanks

 

[ian9999]

Hi Corleone

If you altered the formula in F2 to {=INDEX($C$2:$C$11,SMALL(IF($F$1=$A$2:$A$11,ROW($A$2:$A$11)-ROW($A$2)+1),ROW(1:1)))}

Does this work, for what you want?

And in E2 {=INDEX(B:B,MATCH($F$1&$F2,A:A&C:C,0))}

Regards

Ian

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G
1					Activity 2
2	Activity 1	D1	22/02/2022		Activity 2	D3	04/03/2022
3	Activity 1	D2	27/02/2022		Activity 2	D5	14/03/2022
4	Activity 2	D3	04/03/2022		Activity 2	D7	24/03/2022
5	Activity 1	D4	09/03/2022
6	Activity 2	D5	14/03/2022
7	Activity 1	D6	19/03/2022
8	Activity 2	D7	24/03/2022			`
9	Activity 3	D8	29/03/2022
10	Activity 3	D9	03/04/2022
11	Activity 3	D10	08/04/2022
Sheet1

Cell Formulas
Range	Formula
E2:G4	E2	=FILTER(A2:C11,(C2:C11<=SMALL(IF(A2:A11=E1,C2:C11),3))*(A2:A11=E1))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
E1	List	=$A$7:$A$9

 

[BSALV]

Map1
	A	B	C	D	E	F
1	product	description	qty		description	activity
2	Activity 3	a	16/02/2022		c	18/02/2022
3	Activity 4	b	17/02/2022		g	22/02/2022
4	Activity 1	c	18/02/2022		k	26/02/2022
5	Activity 2	d	19/02/2022		m	2/03/2022
6	Activity 3	e	20/02/2022		m	6/03/2022
7	Activity 4	f	21/02/2022
8	Activity 1	g	22/02/2022
9	Activity 2	h	23/02/2022
10	Activity 3	i	24/02/2022
11	Activity 4	j	25/02/2022
12	Activity 1	k	26/02/2022
13	Activity 2	l	27/02/2022
14	Activity 3	m	28/02/2022
15	Activity 4	m	1/03/2022
16	Activity 1	m	2/03/2022
17	Activity 2	m	3/03/2022
18	Activity 3	m	4/03/2022
19	Activity 4	m	5/03/2022
20	Activity 1	m	6/03/2022
21	Activity 2	m	7/03/2022
22	Activity 3	m	8/03/2022
Blad2

Cell Formulas
Range	Formula
E2:F6	E2	=SORT(FILTER(Tabel1[[description]:[qty]],Tabel1[product]="Activity 1","empty"),2,1)
A2:A22	A2	="Activity " & MOD(ROW(),4)+1
C3:C22	C3	=+C2+1
Dynamic array formulas.

 

[Corleone]

Hi Corleone

If you altered the formula in F2 to {=INDEX($C$2:$C$11,SMALL(IF($F$1=$A$2:$A$11,ROW($A$2:$A$11)-ROW($A$2)+1),ROW(1:1)))}

Does this work, for what you want?

And in E2 {=INDEX(B:B,MATCH($F$1&$F2,A:A&C:C,0))}

Regards

Ian

This works a treat - Many thanks
I havent tried the to other 2 options ,though im sure they work just as well
Thanks all for the responses

 

[Corleone]

A slight issue i have still is that if there is more than 1 activity with the same date - It returns the first Activity Description for all of them

 

[BSALV]

I havent tried the to other 2 options ,though im sure they work just as well

mrexcel (1).xlsx
	A	B	C	D	E	F	G
1	product	description	qty		description	activity
2	Activity 3	a	16/02/2022		c	16/02/2022
3	Activity 4	b	16/02/2022		g	16/02/2022
4	Activity 1	c	16/02/2022		k	16/02/2022
5	Activity 2	d	16/02/2022		m	16/02/2022
6	Activity 3	e	16/02/2022		m	16/02/2022
7	Activity 4	f	16/02/2022
8	Activity 1	g	16/02/2022
9	Activity 2	h	16/02/2022
10	Activity 3	i	16/02/2022
11	Activity 4	j	16/02/2022
12	Activity 1	k	16/02/2022
13	Activity 2	l	16/02/2022
14	Activity 3	m	16/02/2022
15	Activity 4	m	16/02/2022
16	Activity 1	m	16/02/2022
17	Activity 2	m	16/02/2022
18	Activity 3	m	16/02/2022
19	Activity 4	m	16/02/2022
20	Activity 1	m	16/02/2022
21	Activity 2	m	16/02/2022
22	Activity 3	m	16/02/2022
Blad1

Cell Formulas
Range	Formula
E2:F6	E2	=SORT(FILTER(Tabel2[[description]:[qty]],Tabel2[product]="Activity 1","empty"),2,1)
A2:A22	A2	="Activity " & MOD(ROW(),4)+1
Dynamic array formulas.