How to index match in ranked order if cell is greater

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
521
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEF
12
2B3True3L
3D2True5E
4E4True2B
5G0false X
6A2True5D
7K1false A
8M0false 
9X3True3
10L5True1
11
Sheet2
Cell Formulas
RangeFormula
C2:C10C2=IF(B2>=$D$1,"True","false")
D2:D10D2=IF(C2="true",RANK(B2,$B$2:$B$10),"")
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hi

If you could update to Excel 365 this would be possible.
Mappe8
ABC
12
2B3L
3D2E
4E4B
5G0X
6A2D
7K1A
8M0
9X3
10L5
Tabelle1
Cell Formulas
RangeFormula
C2:C7C2=INDEX(SORT(FILTER(A2:B10,B2:B10>=C1),2,-1),,1)
Dynamic array formulas.
 
Upvote 0
Thank you for your suggestion!

Meanwhile is there any other classic formula that can get the same results?
 
Upvote 0
Without Ex365:
Book1
ABCDE
12
2B3L
3D2E
4E4B
5G0X
6A2D
7K1A
8M0 
9X3 
10L5 
Data
Cell Formulas
RangeFormula
E2:E10E2=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),"")
 
Upvote 0
Without Ex365:
Book1
ABCDE
12
2B3L
3D2E
4E4B
5G0X
6A2D
7K1A
8M0 
9X3 
10L5 
Data
Cell Formulas
RangeFormula
E2:E10E2=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 !:biggrin:
Thank you so much, brilliant !
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top