Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • Excel Limits Excel User tips
  • Multiple lines in cell using functions CHAR
  • Schedule column Basic Math
  • BMI Calculator Basic Math
  • Excel ad from early 90s Non-functions
  • CamelCase to Camel Case CHAR
  • Get 3rd Wednesday of month Basic Math
  • Hello world! Uncategorized

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

  • Excel Dynamic Drop-Downs that grow with you COUNTA
  • Calculate hours between two cells ABS
  • Min Max vehicle Formulas - combined functions
  • iframe in Excel (XLiFrame) ADDRESS
  • FORMULATEXT to show formulas FORMULATEXT
  • Sorting with functions only CHOOSE

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
  • Insert Blank rows into table using functions CHAR
  • Sum of divide – Array formula Array formula
  • SimpleANBox Basic Math
  • The Excel “&” bug Excel User tips
  • Multiple Dual-Validations COUNTA
  • Multiple-Validations (connected to each other) Data Validation
  • Walking Columns ActiveX controls
  • Shades of gray Graphics

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme