0
votes

I am in a situation where I need to find the first cell in a range that has the max length of all cells in the range, and return its contents.

I have used MAX(ARRAYFORMULA(LEN({A:A}))) to find the cell with the largest length of characters, which in my specific case, is 14 characters long, but I need to go the next step, and return the contents of that cell.

I would just go CELL("contents", MAX(ARRAYFORMULA(LEN({A:A})))), but that isn't the reference of the cell with the highest value, that is just the number 14.

There can be multiple cells in a range with a 14-character length, so I only need the first instance of it.

I must be so very close to a solution, but just can't quite get there, I thank you in advance for looking into this, Daniel.

2

2 Answers

2
votes
=QUERY(FILTER(A:A,len(A:A)=14),"limit 1")
0
votes

This was solved with the addition of checking the index: CELL("contents",INDEX(F22:F25,MATCH(MAX(LEN(F22:F25)),LEN(F22:F25),0)))

Thanks to Gareth Haage for personally messaging me about the solution.