Hello I need to finish some excel sheet for school and I am having real difficulties.
I would need to find longest FULL name from table like this. The requirements are that I can NOT create another column for making
=Len(A1)+Len(B1)
and then compare just that column for example. So the solution should be only in 1 row of "code".
| A | B | C
1| adam | brown | The longest name is:
2| eva | jameson | john startstky
3| john | startsky |
Any help very appriciated. I already tried
=INDEX(A1:A3,MATCH(MAX(LEN(A1:A3)),LEN(A1:A3),0))
But that only searches through 1 column and does not work with multiple columns.
Note:
I am not skilled in excel that much but here is what I think should work. I just have no clue how to do something like this in excel.
string longestName
For each row
if(A[index of row].lenght+B[index of row].lenght > longestName.lenght)
longestName = A[row].tostring+" "+B[row].tostring
I would also like to avoid any programing in C or basic (I am not sure which language excel supports).
Edit: The code part is only hint for you to understand my thinking procces. I know you cant apply code like this into formula...
We will need two Index function concatenated to get your return. We also need to use Array Forms of MATCH to find the longest concatenation.
=INDEX($A$1:$A$3,MATCH(MAX(LEN(A1:A3 & " " & B1:B3)),LEN(A1:A3 & " " & B1:B3),0)) & " " & INDEX($B$1:$B$3,MATCH(MAX(LEN(A1:A3 & " " & B1:B3)),LEN(A1:A3 & " " & B1:B3),0))
Being an array formula it needs to be confirmed with Ctrl-Shift-Enter when exiting Edit Mode instead of Enter. If done properly then Excel will put {}
around the formula.