Where is the Longest String

Question

I have a small block of cells, say C4 through G11. The cells contain text of various lengths. I can easily get the length of the longest string in the block with the array formula:

=MAX(LEN(C4:G11))

I need a formula to get the address of the cell with this longest string. If there is more than one cell with the longest string, I need the address of the cell closest to the top the the block. If the is more than one cell with the longest string in the same row, I need the address of the one closest to the left edge of the block.

In the above example the formula should return E8.

For this workbook, I can't use VBA. Any suggestions ??

Answer 1

Because it's Gary's Student:

=ADDRESS(MIN(IF(LEN($C$4:$G$11)=MAX(LEN($C$4:$G$11)),ROW($C$4:$G$11))),MIN(IF(LEN($C$4:$G$11)=MAX(LEN($C$4:$G$11)),COLUMN($C$4:$G$11))),4)

It is an array formula so Ctrl-Shift-Enter.