CODEWITHSUNDEEP
excelsortingdynamic
Boosted_d16
Boosted_d16

Reputation: 14062

Excel Dynamic Sorting using Formulas

I want to sort the table below by "Rank" AND if the value in "Rank" is greater than 20.

enter image description here

Expected output:

enter image description here

I found many useful articles but none helped me achieve the expected output.

useful article 1: http://excelbyjoe.com/using-excel-functions-to-dynamically-sort-data/

useful article 2: https://www.extendoffice.com/documents/excel/2549-excel-find-first-last-value-greater-than.html

useful article 3: https://www.extendoffice.com/documents/excel/1050-excel-find-closest-value.html

Any ideas?

Upvotes: 0

Views: 682

Answers (1)

Mrig
Mrig

Reputation: 11702

Assuming your data starts from Cell A2 to Column D. Enter the following formula in the Cell F2

=SMALL(IF($A$2:$A$9>=20,$A$2:$A$9),ROW()-1)

This is an array formula so commit it by pressing Ctrl+Shift+Enter.

Now, in Cell G2 enter the following formula

=VLOOKUP(F2,$A$2:$D$9,2,FALSE)

Then in Cell H2 enter

=VLOOKUP(F2,$A$2:$D$9,3,FALSE)

and finally in Cell I2 enter the following formula

=VLOOKUP(F2,$A$2:$D$9,4,FALSE)

Drag/Copy down all formula as required and change range as needed. See image for reference.

enter image description here

If you want records with RANK greater than 20 just remove the = sign from first formula.

Upvotes: 1

Related Questions