Function Problems

Return Longest and Second-Longest Text with Multiple Criteria in Excel

Steve K Sep 30, 2026 868 views

Question details

The user needs to retrieve the longest and second-longest text strings from a dataset based on specific month and Y/N criteria, returning blanks if no match is found.

How to Return the Longest and Second-Longest Text with Multiple Criteria in Excel

Product: Excel
Device & OS: not provided
Scenario: Extracting the most detailed feedback or longest text entries based on conditional filtering without truncating data.
Observed behavior: The goal is to successfully filter and sort text by string length in descending order, elegantly handling ties and returning a blank cell when there are no matches.

Before you start

Ensure you are using a modern version of Excel (Microsoft 365 or Excel 2021 and later) that supports dynamic array functions such as LET, FILTER, SORTBY, and TAKE.

Solution 1Recommended

Use Dynamic Array Formulas (LET, FILTER, SORTBY, TAKE)

This is the most robust method. It calculates text length, sorts the filtered data descending by length, and retrieves the top two results without breaking on ties.

By combining modern dynamic array functions, you can create a single formula that evaluates criteria, checks the string lengths, and returns the exact text values directly. This avoids the common pitfalls of index matching against duplicate string lengths.

Define conditions with LET and FILTER

Start your formula using the LET function to declare a variable for your filtered data. Use the FILTER function to include only the rows that match your specific month and Y/N criteria.

Calculate lengths and sort

Within the same LET function, use the SORTBY function to sort the filtered array. Use the LEN function on the filtered array as the sort array, setting the sort order to -1 (descending).

Extract the top two results

Wrap the SORTBY function inside the TAKE function, specifying 2 for the rows argument (e.g., TAKE(sorted_array, 2)) to retrieve the longest and second-longest strings.

Handle empty results

Wrap the entire formula in IFERROR(..., "") so that if the FILTER function finds no matches, the cell remains blank instead of displaying an error.

Use Dynamic Array Formulas (LET, FILTER, SORTBY, TAKE)

Preserving Data Integrity: Returning the text values directly ensures that ties are preserved in their original order and exceptionally long text strings are not truncated.

Solution 2

Use FILTER and LARGE with IFERROR

An alternative approach for users who prefer calculating the max length first, though it requires careful handling of duplicate lengths.

Efficient Data Filtering

Extract and Analyze Long Text with WPS Spreadsheet

WPS Spreadsheet fully supports modern dynamic array functions, allowing you to seamlessly filter, sort, and extract the longest text records based on multiple criteria without lag.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your feedback data and criteria.
2. Select the target cell: Click on the cell where you want the longest text to appear.
3. Input the formula: Type your dynamic array formula combining FILTER, SORTBY, and TAKE.
4. Apply and spill results: Press Enter to instantly execute the formula and spill the top results into the adjacent cells.

Fully compatible with Microsoft Excel formulas like FILTER, SORTBY, and IFERROR.Handles complex dynamic array formulas and large datasets efficiently.Free, lightweight, and features a familiar tabbed user interface.Seamless migration of your existing Excel workbooks without formatting loss.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my dynamic array formula return a #SPILL! error?

A #SPILL! error occurs when the formula needs to output multiple values (like both the longest and second-longest text), but the destination cells are not empty. Clear the cells immediately below your formula to allow the results to populate properly.

How does the LET function improve this formula?

The LET function allows you to assign names to calculation results, such as the filtered array. This prevents Excel from calculating the same FILTER function multiple times within one formula, making the formula shorter to read and significantly faster to process.

What if multiple text strings have the exact same longest length?

By using the SORTBY function based on the LEN of the array, ties are naturally preserved in their original top-to-bottom order. The TAKE function will simply return the first two tied results without throwing a duplicate value error.

Excel & Data Problems