Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • Compare2, a tool to compare two sheets using formulas Conditional Formatting
  • CountIf limitation COUNTIF
  • iframe in Excel (XLiFrame) ADDRESS
  • Pixel Excel Drawing Format Cells
  • Fx to cut long column into two columns Formulas - combined functions
  • Cell formats 0.0\% and 0.0%;(0.0%) Format Cells
  • Schedule column Basic Math
  • GoLast to jump to last cell in a column CELL

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

  • Hyperlink usage and HyperlinkOf UDF CELL
  • Get 3rd Wednesday of month Basic Math
  • Links between two workbooks – dynamically using functions INDIRECT
  • Scorring, saving multiple outputs in 1 number Basic Math
  • SimpleANBox Basic Math
  • Insert Blank rows into table using functions CHAR

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
  • Middle name + 1st name Basic Math
  • Hyperlink usage and HyperlinkOf UDF CELL
  • Skills Grid Conditional Formatting
  • Limited Ceiling Basic Math
  • When Excel tells you to go get some life Excel User tips
  • Week number to Month number DATE
  • Workdays – Across months DATE
  • Excel Limits Excel User tips

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme