Need help with the Filter function

[gsdanger]

Hi all at Mr Excel,
I ‘m using the FILTER function to extract data from a website column.
The column displays data from different dates, in the column.
The rows are dynamic and change every day, so I cannot use the row reference in the formula.
I want to extract (for example) the 2nd entry from column J into a new cell.
Can I do this with the Filter function?

The rows Change every day.	Dates​	Dist​	​	Result of Extraction​
ROW	I​	J​	​
20	01 Mar 25	2500​		2200​
21	03 Mar 25	2200​
22	08 Mar 25	1590​
23
24..ETC…

My Function formula is:
=Filter($J$21:$J24,$J$20:$J$23=Dist) ?? I'm sure this formula is incomplete, and will not work.
I need your assistance please.

Kind Regards.

Gsdanger.

 

[Eric W]

You can use the INDEX function:

=INDEX($J$20:$J$24,2)

 

[gsdanger]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2															=INDEX($J$5:$J$14,2)		I TRIED TO INCORPORATE THE INDEX AND THE FILTER FORMULAS TO ACHIEVE MY GOAL.ALAS, IT DIDN'T WORK..
3												I WANT THE 2ND ENTRY OF EACH RECORD TO BE EXTRACTED.
4															The result of the above formula is below:
5										Dist							=INDEX(FILTER($I$5:$J$13,$J$5:$J$13),2)
6							1		1	2500		1	2200	1	2200		0	2200
7										2200		2	1450	2	2200		0	2200
8										1950		3	1950	3	2200		0	2200
9							2		2	1250					2200		0	2200
10										1450					2200		0	2200
11										1600					2200		0	2200
12							3		3	2000					2200		0	2200
13										1950					2200		0	2200
14										1650					2200		0	2200
15
16
17
Sheet1

Cell Formulas
Range	Formula
O6:O14	O6	=INDEX($J$6:$J$15,2)
Q6:R14	Q6	=INDEX(FILTER($I$6:$J$14,$J$6:$J$14),2)
Dynamic array formulas.

 

[gsdanger]

Hi Eric,
Thank you for your reply. I have tried to get the formulas working, however with no luck. The option you gave me, seems to select the entry number (1,2, ro 3) instead of the 2nd entry of each record.Can you please help me with this problem?
In anticipation of your response, Thank you.

Kind Regards
gsdanger

 

[Eric W]

You can do something like this:

Book1
	G	H	I	J	K	L	M
1
2
3						I WANT THE 2ND ENTRY OF EACH RECORD TO BE EXTRACTED.
4
5				Dist
6	1		1	2500		1	2200
7				2200		2	1450
8				1950		3	1950
9	2		2	1250
10				1450
11				1600
12	3		3	2000
13				1950
14				1650
15
Sheet7

Cell Formulas
Range	Formula
L6:M8	L6	=LET(k,I6:I20,r,J6:J20,f,FILTER(k,k<>""),t,INDEX(r,MATCH(f,k,0)+1),HSTACK(f,t))
Dynamic array formulas.

More details were helpful. In this case, the +1 in the formula means you want the row 1 below the key.

 

[gsdanger]

Thanks Eric,
That worked...Hooray. Once again, thank you.
kind Regards...
gsdanger