How to index match in ranked order if cell is greater

[gaftalik]

Hi everyone,
in A2:10 i have names
in B2:B10 i have results
in D1: i have a condition

In E2:E10, i need to return the name of the cells in range A2:A10 in a rank order only if their results in B2:B10 is greater than D1
So i created in C2:C10 an if formula to check if every result is greater
Then in D2:D10 i used a rank formula if True
So far i couldnt finish the formula to rank them in order as answer in E2:E10

Is there any formula to continue or to return same answers without the need of other formulas in columns C and D?

Thank you for your help!

Book1.xlsx
	A	B	C	D	E	F
1				2
2	B	3	True	3	L
3	D	2	True	5	E
4	E	4	True	2	B
5	G	0	false		X
6	A	2	True	5	D
7	K	1	false		A
8	M	0	false
9	X	3	True	3
10	L	5	True	1
11
Sheet2

Cell Formulas
Range	Formula
C2:C10	C2	=IF(B2>=$D$1,"True","false")
D2:D10	D2	=IF(C2="true",RANK(B2,$B$2:$B$10),"")

 

[shift-del]

Hi

If you could update to Excel 365 this would be possible.

Mappe8
	A	B	C
1			2
2	B	3	L
3	D	2	E
4	E	4	B
5	G	0	X
6	A	2	D
7	K	1	A
8	M	0
9	X	3
10	L	5
Tabelle1

Cell Formulas
Range	Formula
C2:C7	C2	=INDEX(SORT(FILTER(A2:B10,B2:B10>=C1),2,-1),,1)
Dynamic array formulas.

 

[gaftalik]

Thank you for your suggestion!

Meanwhile is there any other classic formula that can get the same results?

 

[bebo021999]

Without Ex365:

Book1
	A	B	C	D	E
1				2
2	B	3			L
3	D	2			E
4	E	4			B
5	G	0			X
6	A	2			D
7	K	1			A
8	M	0
9	X	3
10	L	5
Data

Cell Formulas
Range	Formula
E2:E10	E2	=IFERROR(LOOKUP(2,1/(($B$2:$B$10-ROW($B$2:$B$10)*10^-5)/($B$2:$B$10>=$D$1)=AGGREGATE(14,6,($B$2:$B$10-ROW($B$2:$B$10)*10^-5)/($B$2:$B$10>=$D$1),ROWS($1:1))),$A$2:$A$10),"")

 

[gaftalik]

Without Ex365:

Book1
	A	B	C	D	E
1				2
2	B	3			L
3	D	2			E
4	E	4			B
5	G	0			X
6	A	2			D
7	K	1			A
8	M	0
9	X	3
10	L	5
Data

Cell Formulas
Range	Formula
E2:E10	E2	=IFERROR(LOOKUP(2,1/(($B$2:$B$10-ROW($B$2:$B$10)*10^-5)/($B$2:$B$10>=$D$1)=AGGREGATE(14,6,($B$2:$B$10-ROW($B$2:$B$10)*10^-5)/($B$2:$B$10>=$D$1),ROWS($1:1))),$A$2:$A$10),"")

Oh man this is too much science & physics for the brain, it looks like an outer space formula. It definitely got the job done but will never be able to learn it !
Thank you so much, brilliant !