Find longest string

The goal is to find the longest text string in the range B5:B16. At the core, this is a lookup problem that requires creating a value (the string length) that does not exist in the data as part of the formula. The easiest way to solve this problem is with the XLOOKUP function or the FILTER function. However in older versions of Excel without the XLOOKUP function, you can use an INDEX and MATCH formula. Both approaches are explained below. For convenience, the range B5:B16 is named data. However, you can use a regular cell reference as well.

XLOOKUP solution

In the workbook shown, the XLOOKUP function is used to return the longest name in the range B5:B16. The formula in E6 is:

=XLOOKUP(MAX(LEN(data)),LEN(data),data)

Where data is the named range B5:B16. The result is "Esmeralda", which contains 9 characters. Working from the inside out, we first use the LEN and MAX functions to get the length of the longest name like this:

MAX(LEN(data))

The LEN function returns the length of a text string in characters. In this case, there are 12 names in data (B5:B16) so LEN returns 12 results in an array like this:

{5;6;8;6;6;5;6;8;9;6;8;6}

Each number represents the length in characters of one name in the data. The result from LEN is returned directly to the MAX function, which returns 9:

MAX({5;6;8;6;6;5;6;8;9;6;8;6}) // returns 9

The result from MAX is delivered to XLOOKUP as the lookup_value:

=XLOOKUP(9,LEN(data),data)

At this point, we have a lookup value of 9 and we need to create a lookup array that holds all string lengths. To do this we call the LEN function again, the same as before:

LEN(data) // returns {5;6;8;6;6;5;6;8;9;6;8;6}

The result from LEN becomes the lookup_array:

=XLOOKUP(9,{5;6;8;6;6;5;6;8;9;6;8;6},data)

XLOOKUP performs an exact match by default. It matches the 9 in the lookup array and returns the corresponding value in data ("Esmeralda") as a final result. 

Note: with the worksheet as shown, the result from XLOOKUP and FILTER is the same, since there are no ties. However, if there were two names with a length of 9 characters, FILTER would return both names and the XLOOKUP formula would return just the first name with 9 characters.

FILTER solution

Another way to solve this problem is with the FILTER function, which is designed to retrieve multiple matching records. This makes sense in cases where there may be ties, and you would like to report all results. To get the longest string with FILTER, including any tie values, the formula is:

=FILTER(data,LEN(data)=MAX(LEN(data)))

The logic in this formula is similar to what we used above with XLOOKUP. We are asking FILTER for all values in data where the length of the text string equals the max length found in data.

INDEX and MATCH solution

In older versions of Excel without the XLOOKUP function, you can use an array formula based on INDEX and MATCH like this:

{=INDEX(data,MATCH(MAX(LEN(data)),LEN(data),0))}

Note: enter as an array formula with control + shift + enter in Excel 2019 and earlier.

Most of the hard work in this formula is done with the MATCH function, which is set up like this:

MATCH(MAX(LEN(data)),LEN(data),0)

Here, MATCH is set up to perform an exact match by supplying zero for match_type. For lookup_value, we supply the maximum string length found in all data:

MAX(LEN(data))

LEN returns an array of results (lengths), one for each name in the list:

{5;6;8;6;6;5;6;8;9;6;8;6}

MAX then returns the largest value in the array (9):

MAX({5;6;8;6;6;5;6;8;9;6;8;6}) // returns 9

For the lookup_array, LEN is again used to return an array that contains all lengths:

LEN(data) // returns {5;6;8;6;6;5;6;8;9;6;8;6}

After LEN and MAX run, we have the following:

MATCH(9,{5;6;8;6;6;5;6;8;9;6;8;6},0)

MATCH then returns the position of the max value (9) directly to the INDEX function as the row_num:

=INDEX(data,9)

Finally, the INDEX function returns the value in the 9th row of data, which is "Esmeralda".

Note: it is a coincidence only that the longest name (9 characters), happens to be in the 9th row!