I have this spreadsheet with three different columns (A-B-C). Now I need a cell with a formula to find the MAX value in the column A and write there the contents of the columns A,B,C.
For instances:
A B C
1 a Z
5 x K
3 f T
2 r H
The cell should then show up 5 x K.
Any idea?
This seems contradictory to me. To output “multiple columns” (the contents assumably) I would need as many columns for the output, and therefore an array-formula. If you actually want to join (concatenate) contents from 3 columns in one cell you need to consider that you cannot access each single content from that output with reasonable effort since there is no TEXTSPLIT() function in Calc.
AND
As often with similar questions you seem to assume there is a unique “row of maximum” in column A. Since this mostly cannot be assured reliably, your results may be misleading in respective cases.
Hello
you may use (assuming you start in row 1)
=TEXTJOIN(" ";1;INDIRECT("A" & MATCH(MAX(A1:A4);A1:A4;0) & ":C" & MATCH(MAX(A1:A4);A1:A4;0)))
Note: If there are several rows having the max value, the first one will be found and its row will be evaluated.
Tested using LibreOffice:
Version: 6.4.0.3, Build ID: b0a288ab3d2d4774cb44b62f04d5d28733ac6df8
CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kf5;
Locale: en-US (en_US.UTF-8); UI-Language: en-US, Calc: threaded
Hope that helps.
If the answer helped to solve your problem, please click the check mark (
) next to the answer.
I think I will have to upgrade my LibreOffice… my version is 4.1.1.2!
Since you did neither mention your operating system nor your LibreOffice version, my answer could not factor in such dependencies.
I updated it and It kind of work. The concatenation part is OK but for some weird reason it is not selecting the row with the MAX value. It’s selecting a row with a value around the middle between the minimum and maximum…