Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • UDF – Convert Number into text (English and Arabic) User-Defined f(x)s = UDF
  • Formula Beautifier Excel User tips
  • MATCH fumction, just a simple search Lookup and References
  • Search across sheets in function ADDRESS
  • Workdays – Across months DATE
  • Zipcode-State search Formulas - combined functions
  • Validation based on another Validation COUNTA
  • Change Calculator – calculate change in multiple bills/coins Formulas - combined functions

Sort list dynamically (functions)

Posted on February 5, 2012 By ANmar No Comments on Sort list dynamically (functions)

Sorting a list automatically using formulas, with no need to press the sort command
Also if the source table is changed, the destination table will do also.

What you need is basically two formulas, one for the sort-by column to list items by order. Use SMALL to sort ascending, or LARGE to sort descending, then another formula to retrieve other columns data based on the sort-by column.

Sort-by column (Ascending order):

=SMALL($D$8:$D$11,ROW()-7)

Sort-by column (Descending order):

=LARGE($D$8:$D$11,ROW()-7)

And you can either use a regular VLOOKUP to get other columns data (assuming the sort-by column is the first column in  original list, or, you can use INDEX with MATCH to get all columns not mater where is the sort-by column located, like this one:

=INDEX($B$8:$D$11,MATCH(L8,$D$8:$D$11,0),1)

Screenshots and attached file should be clear to see and modify…

COLUMN, Formulas - combined functions, INDEX, LARGE, Lookup and References, MATCH, Math and Trig, ROW, SMALL, Standard functions

Post navigation

Previous Post: Insert Blank rows into table using functions
Next Post: Rotate table using 1 formula

Related Posts

  • Sum of divide – Array formula Array formula
  • FORMULATEXT to show formulas FORMULATEXT
  • Count how many times letter found Formulas - combined functions
  • Unix DateTime Number Basic Math
  • SimpleANBox Basic Math
  • Extract Address into table Formulas - combined functions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • Stale values (Scratched functions)
  • Middle name + 1st name
  • LastSunday, LastSaturday
  • Paste Special Percentage
  • Jan8 date format

Archives

Categories

  • Array formula (1)
  • Formulas – combined functions (58)
  • Google Sheets (2)
  • Non-functions (36)
    • ActiveX controls (2)
    • Conditional Formatting (7)
    • Data Validation (7)
    • Excel User tips (19)
    • Format Cells (6)
    • Graphics (4)
    • Names (3)
  • Standard functions (82)
    • Basic Math (18)
    • Date and Time (16)
      • DATE (9)
      • Hour (1)
      • MONTH (7)
      • NETWORKDAYS (2)
      • TODAY (7)
      • WEEKDAY (8)
      • WEEKNUM (1)
      • YEAR (8)
    • Engineering (1)
      • CONVERT (1)
    • Information (6)
      • CELL (4)
      • ISERROR (1)
      • N (1)
    • Logical (30)
      • AND (5)
      • IF (24)
      • IFERROR (6)
      • IFNA (1)
      • IFS (1)
      • ISERROR (1)
      • OR (2)
    • Lookup and References (36)
      • ADDRESS (4)
      • CHOOSE (3)
      • COLUMN (7)
      • FORMULATEXT (1)
      • HLOOKUP (1)
      • HYPERLINK (2)
      • INDEX (5)
      • INDIRECT (6)
      • MATCH (12)
      • OFFSET (15)
      • ROW (11)
      • VLOOKUP (5)
    • Math and Trig (24)
      • ABS (1)
      • CEILING (1)
      • COUNT (1)
      • COUNTA (4)
      • INT (9)
      • LARGE (2)
      • MAX (1)
      • Min (2)
      • SMALL (3)
      • SUM (3)
      • SUMIF (1)
      • SUMIFS (2)
      • SUMPRODUCT (1)
    • Statistical (5)
      • COUNTIF (4)
      • COUNTIFS (1)
    • Texts and Strings (24)
      • CHAR (5)
      • CONCATINATE (1)
      • FIND (1)
      • LEFT (8)
      • Len (4)
      • MID (9)
      • REPT (3)
      • RIGHT (2)
      • SEARCH (9)
      • STRING (1)
      • SUBSTITUTE (2)
      • TEXT (4)
      • TRIM (2)
      • VALUE (2)
  • Uncategorized (2)
  • User-Defined f(x)s = UDF (3)
    • HyperlinkOf (1)
  • Worksheet (16)
  • XLfxs (2)

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
  • CamelCase to Camel Case CHAR
  • Change Calculator – calculate change in multiple bills/coins Formulas - combined functions
  • VLookup Lookup and References
  • Cell formats 0.0\% and 0.0%;(0.0%) Format Cells
  • Dynamic selection list ActiveX controls
  • OFFSET+MATCH Formulas - combined functions
  • Get monthly total from table with dates DATE
  • Skills Grid Conditional Formatting

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme