A
B
C
D
1
List (with spaces)
List (without spaces)
2
a
a
f
=INDEX($A:$A,MATCH(REPT("z",26),$A:$A,1))
3
b
b
4
=INDEX($A:$A,MATCH(9.999+35,$A:$A,1))
4
c
c
4
=INDEX($A:$A,MAX(MATCH(REPT("z",26),$A:$A,1),MATCH(9.999+35,$A:$A,1)))
5
1
nrList
=Sheet2!$A$2:INDEX(Sheet2!$A:$A,MAX(MATCH(REPT("z",26),Sheet2!$A:$A,1),MATCH(9.999+35,Sheet2!$A:$A,1)))
6
1
2
10
=SUM(nrList)
7
2
3
11
=COUNT(B:B)+COUNTIFS(B:B,">=A",B:B,"<=z")
8
3
d
9
e
4
=INDEX($B:$B,COUNT(B:B)+COUNTIFS(B:B,">=A",B:B,"<=z"))
10
d
f
4
=INDEX($B:$B,MATCH(9.999+35,$B:$B,1))
11
e
4
nrList2
=Sheet2!$B$2:INDEX(Sheet2!$B:$B,COUNT(Sheet2!A:A)+COUNTIFS(Sheet2!A:A,">=A",Sheet2!A:A,"<=z"))
12
f
13
4
14
2
DV: =nrList
15
b
DV: =nrList2
16