Returning ?th last entry from a list

[Steve 1962]

Hi All

Require a formula that returns the 9th last entry (text) in a list. Directly under this, also need a formula that returns the 8th last entry (text) in the same list, and so on ……7th, 6th………………..

Thanks

Steve

 

[DanteAmor]

How about:

varios 09ago2020.xlsm
	A	B	C	D
1
2	Text a		Text d	9 th last entry
3	Text b		Text e	8 th last entry
4	Text c		Text f	7 th last entry
5	Text d		Text g	6 th last entry
6	Text e		Text h	5 th last entry
7	Text f		Text i	4 th last entry
8	Text g		Text j	3 rd last entry
9	Text h		Text k	2 nd last entry
10	Text i		Text l	1 sh last entry
11	Text j		Text m	last entry
12	Text k
13	Text l
14	Text m
Hoja3

Cell Formulas
Range	Formula
C2:C11	C2	=INDEX($A$2:$A$14,COUNTA($A$2:$A$14)-(9-(ROWS(B$2:B2)-1)))

 

[Steve 1962]

Hey thanks DanteAmor. Works absolutely great.

Have a nice day.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Peter_SSs]

Works absolutely great.

Did you adjust at all? For my understanding of the terms, "Text d" is the 10th last entry, "Text l" is the 2nd last entry etc.

Since you have Excel 365 you should also be able to do this by entering a formula in one cell only, like this in C2. The other results automatically 'spill' to the other rows. For more or less results, adjust the 9 & 8 values in the SEQUENCE function.

20 08 10.xlsm
	A	B	C	D
1
2	Text a		Text e	9 th last entry
3	Text b		Text f	8 th last entry
4	Text c		Text g	7 th last entry
5	Text d		Text h	6 th last entry
6	Text e		Text i	5 th last entry
7	Text f		Text j	4 th last entry
8	Text g		Text k	3 rd last entry
9	Text h		Text l	2 nd last entry
10	Text i		Text m	last entry
11	Text j
12	Text k
13	Text l
14	Text m
Last n

Cell Formulas
Range	Formula
C2:C10	C2	=INDEX(A2:A14,SEQUENCE(9,,ROWS(A2:A14)-8))
Dynamic array formulas.