Tom’s Tutorials For Excel: Finding the Attributes of the Maximum Length Value in a List

Tom’s Tutorials For Excel: Finding the Attributes of the Maximum Length Value in a List

Here are formulas that return various attributes of the lengthiest value in a list. In the pictures are formulas for the lengthiest value itself; its length; its row in the list; and its cell address.

These are all array formulas. Recall, an array formula is applied to the cell by pressing
Ctrl+Shift+Enter, not just Enter.

If you are unfamiliar with array formulas, see my video and explanation of arrays here.

Formula for maximum value:
=INDEX(A4:A10,MATCH(MAX(LEN(A4:A10)),LEN(A4:A10),0))


Formula for maximum value length:
=MAX(LEN(A4:A10))


Formula for maximum value row (index number row in range of interest):
=MATCH(MAX(LEN(A4:A10)),LEN(A4:A10),0)


Formula for maximum value cell address:
=ADDRESS(MAX(ROW(A4:A10)*(LEN(A4:A10)=MAX(LEN(A4:A10)))),1)