To build a live list of matching rows and keep it sorted, enter one formula in a clear worksheet cell: =SORT(FILTER(A2:D100,C2:C100=H1,""),4,-1). It returns rows from A2:D100 where the corresponding value in column C matches H1, then sorts those results by their fourth column in descending order. Change the ranges, match condition, sort column, and direction to fit your sheet.
How the FILTER + SORT formula works
FILTER selects rows using a Boolean test, and SORT orders the resulting array. Microsoft describes FILTER as a way to filter a range of data based on criteria you define. The formula nests the functions so sorting applies to the matches, not to the original data.
As an Amazon Associate I earn from qualifying purchases.
In =SORT(FILTER(A2:D100,C2:C100=H1,""),4,-1), A2:D100 is the source data, C2:C100=H1 checks each row against the chosen criterion, and "" supplies a result when nothing matches. The outer function sorts the matches by column four of the returned array, descending.
Build the formula for your worksheet
-
Identify the full set of columns to return, such as
A2:D100. The include test must cover the same rows as that source range.#1 Best Overall
Sale
The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
-
Choose a criterion cell, such as
H1, and the source column to test. For example,C2:C100=H1keeps rows whose column C value equals the value in H1. -
Set the no-match result as the third FILTER argument. In
FILTER(array,include,[if_empty]), this argument is optional; without it, no matching rows can result in#CALC!because Excel does not support empty arrays. -
Wrap the FILTER formula in SORT, then set the sort index and direction. In
SORT(array,[sort_index],[sort_order],[by_col]), the index is relative to the returned array. A value of4means its fourth column, while-1requests descending order. The default sort order is ascending.Recommended: Update Every Outdated Driver on Your PC in One Scan - Free →Recommended: Fix Windows Errors and Clear Junk Files in Minutes - Free Scan →Recommended: Crashes or Glitches? A Free Driver Scan Usually Finds the Culprit →Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
Enter the completed formula in the top-left cell where you want the output list to begin. Keep the cells where results may appear clear.
Filter on more than one condition
Combine Boolean tests inside FILTER’s include argument. Microsoft’s examples use multiplication when every condition must be true and addition when either condition may be true.
-
Both conditions (AND):
=SORT(FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),""),4,-1)returns rows where column C matches H1 and column A matches H2. -
Either condition (OR):
=SORT(FILTER(A5:D20,(C5:C20=H1)+(A5:A20=H2),""),4,-1)returns rows where either test is true.Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Repair Windows errors before they cause bigger problems3Fix the driver behind crashes, sound loss and screen glitchesSpecial offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy.
Make the source range grow with your data
Fixed references such as A2:D100 work when the source size is stable. If records will be added or removed, format the source as an Excel table and use structured references so the formula’s source adjusts with the table.
Place the formula outside the table. Microsoft states that spilled array formulas are not supported inside Excel tables. For example, if a table is named Sales and has columns named Region and Revenue, the source and condition can use structured references; use the names from your own table in the formula. The output still needs a clear spill area.
Rank #4
Choose SORT or SORTBY when columns may change
SORT identifies the sort key by its position within the returned array. That is straightforward for a stable layout, but inserting or deleting columns can change which field occupies a given index.
SORTBY sorts an array using a corresponding range instead of a numeric column index. Microsoft describes it as more flexible for grid data and notes that it respects column additions or deletions because it refers to a range. Consider it when the source layout may change; for a fixed shape, the nested SORT formula is simpler.
Free tools Windows power users keep installed
One-click scans. No signup required.
Fix common errors and sharing problems
-
#SPILL!: One or more cells needed for the result are occupied. Clear the output area, including cells beneath and to the right of the formula where results need to appear. -
#CALC!when there are no matches: Add the optional third FILTER argument, such as"", to define the no-match result. -
An error in the include range: FILTER returns an error if its include array contains an error or cannot be converted to Boolean. Check the cells used by the criterion and make sure the include test produces usable true/false values.
-
#REF!with data in another workbook: Microsoft documents limited support for dynamic arrays linked across workbooks. They are supported only while both workbooks are open; closing the source workbook can cause#REF!when the formula refreshes.Recommended Free Tools
Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
A recipient cannot use the formula’s spill behavior: Check the recipient’s Excel edition and version before sharing. Microsoft lists FILTER and SORT for Microsoft 365, Excel 2024, and Excel 2021 across the desktop, Mac, and mobile editions covered by its documentation. Older versions without dynamic-array support do not provide the same spill behavior.
Check Excel compatibility before sharing
Microsoft’s function documentation lists FILTER and SORT for Microsoft 365, Excel 2024, and Excel 2021 in the editions it covers. Microsoft says dynamic arrays were introduced in September 2018 and released to Microsoft 365 subscribers in Current Channel in January 2020. Availability can vary by version and platform, so check the recipient’s Excel release rather than assuming the formula will spill in an older installation.
For details, see Microsoft’s FILTER function documentation, SORT function documentation, SORTBY function documentation, and dynamic array and spill behavior guidance.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
