5

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


enter image description here

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

Gary's Student
  • 19,266
  • 6
  • 25
  • 39

1 Answers1

7

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.

enter image description here

Scott Craner
  • 22,693
  • 3
  • 21
  • 25
  • 1
    That's pretty crafty. I was wondering why you only comment answers. – Raystafarian Apr 12 '16 at 16:46
  • Cool I was writing something to deserve my nick... too late :) – Hastur Apr 12 '16 at 16:47
  • 1
    @Raystafarian I am generally a lazy person and if a comment will answer the question then I will use the shortest route. :) – Scott Craner Apr 12 '16 at 16:48
  • I have to be perfectly honest, I don't understand how the IF array of true/false is matching up to the `row` and `column` formulas? – Raystafarian Apr 12 '16 at 16:54
  • 2
    @ScottCraner...........This is **beautiful**...............thank you very much! – Gary's Student Apr 12 '16 at 16:55
  • The row and column ranges are iterating in sync with the IF portion only the the row numbers or column number that line up with a true are then evaluated with the MIN. The lowest is then returned to the Address function. I probably made it worse. @Raystafarian – Scott Craner Apr 12 '16 at 17:01
  • 1
    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. – Raystafarian Apr 12 '16 at 17:07
  • 1
    And the column is going `{3,4,5,6,7,3,4,5,6,7}` I feel ignorant now. Anyway you got my +1 – Raystafarian Apr 12 '16 at 17:23