To find the longest string (name, word, etc.) in a column, you can use an array formula based on INDEX and MATCH, together with LEN and MAX. In the example shown, the formula in F6 is:


Where "names" is the named range C5:C14.

Note: this is an array formula and must be entered with control + shift + enter.

Generic formula



The key to this formula is the MATCH function, which is set up like this:


In this snippet, MATCH is set up to perform an exact match by supplying zero for match type. For lookup value, we have this:


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


The MAX function then returns the largest value, 9 in this case. For lookup array, LEN is again used to return an array of lengths. The after LEN and MAX run, we have:


which returns the position of the max value, 8.

This goes into INDEX like this:


INDEX duly returns the value in the 8th position of names, which is "Stephanie".

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.