# Where is the Longest String

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 ??

• Might this answer help? It's for only one column though, but it's a starting point. Apr 12, 2016 at 16:38
• @Raystafarian Thanks...............I will take a look. Apr 12, 2016 at 16:44

``````=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)
• Ah, I figured it was something like that, I guess that's because the row formula is being used as an array as well? So it's going `{4,4,4,4,4,5,5,5,5,5 ...`? That is beautiful. Apr 12, 2016 at 17:07
• And the column is going `{3,4,5,6,7,3,4,5,6,7}` I feel ignorant now. Anyway you got my +1 Apr 12, 2016 at 17:23