How to Use FILTER and SORT Instead of a BETWEEN Function in Excel
Question details
The user wants to extract specific names associated with numbers that fall between two defined limits and needs the final results to be optionally sorted.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Extracting a dynamic list of records matching a numerical range since a native BETWEEN formula does not exist.
- Observed behavior
- To successfully return a dynamic, automatically updating list of names associated with numbers within two boundaries, effectively replicating the logic of a BETWEEN function.
Verify that you are using a modern version of Excel (Microsoft 365, Excel 2021, or newer) since FILTER and SORT are dynamic array functions that will not work in older versions.
Use FILTER and SORT to Replicate BETWEEN Logic
Combine the FILTER function with multiplication operators (*) for multiple conditions, and optionally wrap it in a SORT function to organize the output.
Because Excel does not have a dedicated BETWEEN function, you can multiply two logical tests together within the FILTER function. The asterisk (*) acts as an AND operator, meaning both the lower-limit and upper-limit conditions must be true.
Click on the empty cell where you want the top of your filtered and sorted list to appear. Ensure there is empty space below it for the results to spill.
Type `=FILTER(A:A, (B:B>=C2)*(B:B<=D2), "No match")` where Column A contains the names you want to return, Column B contains the numbers to check, C2 is your lower limit, and D2 is your upper limit.
To organize your results, wrap the previous formula in SORT: `=SORT(FILTER(A:A, (B:B>=C2)*(B:B<=D2), "No match"), , 1)`. The '1' dictates ascending order.
Press the Enter key. The array will automatically extract and display all names that correspond to the numbers between your specific criteria.
Use the Advanced Filter Feature (For Older Excel Versions)
If you are using an older version of Excel without dynamic array support, you can use the built-in Advanced Filter tool with a custom criteria range.
Filter and Sort Data Easily with WPS Office
WPS Office Spreadsheet fully supports dynamic array functions like FILTER and SORT. You can easily create custom multi-condition formulas for your data analysis in a familiar interface.
- 1. Open your dataset: Launch WPS Office Spreadsheet and open the workbook containing the names and numeric values.
- 2. Enter the FILTER array formula: Click into an empty cell and enter `=FILTER(A:A, (B:B>=C2)*(B:B<=D2), "No match")` to replicate the BETWEEN behavior.
- 3. Nest within a SORT function: Update the formula to `=SORT(FILTER(A:A, (B:B>=C2)*(B:B<=D2), "No match"))`.
- 4. View your spilled results: Press Enter to instantly apply the dynamic array and view your organized data spilling downwards.
Frequently Asked Questions
Why is my FILTER formula returning a #CALC! error?
The #CALC! error usually occurs when the FILTER function finds no matches and the optional [if_empty] argument is left blank. You can fix this by adding a text string at the end of your formula, such as `=FILTER(A:A, (B:B>=C2)*(B:B<=D2), "No match")`.
Can I use multiple criteria with the FILTER function in Excel?
Yes. You can use multiple criteria by enclosing each logical test in parentheses and multiplying them together using an asterisk (*). This acts as an AND logic operator, which is exactly how you mimic BETWEEN limits.
Do the FILTER and SORT formulas work in older versions of Excel?
No, FILTER and SORT are Dynamic Array functions available only in Microsoft 365, Excel 2021, and newer versions. If you are using an older version, you must rely on alternative methods such as the Advanced Filter tool, PivotTables, or a combination of INDEX and AGGREGATE.
How do I sort by a different column using the SORT function?
The SORT function includes a [sort_index] argument. If your filtered array returns three columns and you want to sort by the second column, you would write `=SORT(FILTER(...), 2, 1)`, where '2' is the column index and '1' is for ascending order.
