Where “names” is the named range C5:C14. Note: this is an array formula and must be entered with control + shift + enter. 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
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.
