Find longest String in Range, and Reflect in Cell

[ECUPIRATE]

Hi!

I am trying to make a formula where the longest word in a range from a different worksheet, is reflected in the same cell where I create the formula.

For example,

Cell A4 should have the formula
and the Range would be located in a Worksheet labeled Descriptions
Cell A4 would show the longest word, with a successful formula

I can't seem to get the follow array formula to work:
=CELL("",INDEX(Descriptions!A3:D3,MATCH(MAX(LEN(Descriptions!A3:D3)),LEN(Descriptions!A3:D3),0)))

I'm hoping to not have to use it, maybe there is a better way?
Please help.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=INDEX(A3:D3,MATCH(MAX(LEN(A3:D3)),LEN(A3:D3),0))<strike></strike>

 

[ECUPIRATE]

Thank you!! That worked - I thought if I put an IF function with it, it would work, but it's not:

=IF(D3="x",INDEX(Descriptions!A3:D3,MATCH(MAX(A3:D3)),LENA3:D3),0)),D3)

...If I could just get this right, my worksheet would work- Then I can go home, and sleep lol. I feel like I'm close. Have any ideas?

 

[Aladin Akyurek]

Thank you!! That worked - I thought if I put an IF function with it, it would work, but it's not:

=IF(D3="x",INDEX(Descriptions!A3:D3,MATCH(MAX(A3:D3)),LENA3:D3),0)),D3)

...If I could just get this right, my worksheet would work- Then I can go home, and sleep lol. I feel like I'm close. Have any ideas?

What is your intent? If D3 = x, do the suggested INDEX formula, otherwise return D3? If so, apply control+shift+enter to the formula you extended.

 

[ECUPIRATE]

Thank you!! That worked - I thought if I put an IF function with it, it would work, but it's not:

=IF(D3="x",INDEX(Descriptions!A3:D3,MATCH(MAX(A3:D3)),LENA3:D3),0)),D3)

...If I could just get this right, my worksheet would work- Then I can go home, and sleep lol. I feel like I'm close. Have any ideas?

I am trying to have it where if I place an x in cell D3, the longest word will show. But if there is no x is cell D3, then nothing would show.

 

[Aladin Akyurek]

I am trying to have it where if I place an x in cell D3, the longest word will show. But if there is no x is cell D3, then nothing would show.

You can't have a manual entry in a cell and a formula in that cell at the same time...

 

[ECUPIRATE]

I am trying to have it where if I place an x in cell D3, the longest word will show. But if there is no x is cell D3, then nothing would show

 

[ECUPIRATE]

I'm sorry, I used the wrong terminology... not if I placed it.... but if there is already an x there

 

[ECUPIRATE]

You can't have a manual entry in a cell and a formula in that cell at the same time...

Thanks for your help - I will clarify -> I mean if there is already an x is cell D3, I would like for the array formula to spit out the longest word, otherwise, whatever is in cell D3 would should show

 

[Aladin Akyurek]

Thanks for your help - I will clarify -> I mean if there is already an x is cell D3, I would like for the array formula to spit out the longest word, otherwise, whatever is in cell D3 would should show

Control+shift+enter, not just enter:

=IF(D3="x",INDEX(Descriptions!A3:D3,MATCH(MAX(A3:D3)),LENA3:D3),0)),D3)