Lookup for top value in each column and output row name

[raikks]

	colA	colB
row1	3​	7​
row2	5​	4​
row3	12​	7​

Lookup for the top value in each column. then output the row's name.

Ex.
* for colA -> output row3
* for colB -> output row1 & row3 (if possible)

 

[Bo_Ry]

Please try

Book1
	A	B
1	3	7
2	5	4
3	12	7
4
5	Row3	Row1, Row3
Sheet1

Cell Formulas
Range	Formula
A5:B5	A5	=TEXTJOIN(", ",,INDEX(REPT("Row"&ROW(A1:A3),A1:A3=MAX(A1:A3)),))

 

[raikks]

Thanks for your reply.
This partially solves my problem.
Maybe my example wasn't very clear because the row names are important - let me rename them:
* row1 - apples
* row2 - bananas
* row3 - oranges

I don't mind using an additional function to replace text.

 

[Peter_SSs]

Try this

20 10 11.xlsm
	A	B	C
1	apples	3	7
2	bananas	5	4
3	oranges	12	7
4
5		oranges	apples, oranges
Textjoin

Cell Formulas
Range	Formula
B5:C5	B5	=TEXTJOIN(", ",1,IF(B1:B3=MAX(B1:B3),$A1:$A3,""))

 

[raikks]

Unfortunately, it doesn't work - copy pasted exactly the same example as above and the result is #VALUE!
I'm not sure why...

 

[Bo_Ry]

You need to confirm Peter_SSs's formula with Ctrl+Shift+Enter

or this with normal enter

Book1
	A	B	C
1	apples	3	7
2	bananas	5	4
3	oranges	12	7
4
5		oranges	apples, oranges
Sheet1

Cell Formulas
Range	Formula
B5:C5	B5	=TEXTJOIN(", ",,INDEX(REPT($A1:$A3,B1:B3=MAX(B1:B3)),))

 

[VBasic2008]

Unfortunately, it doesn't work - copy pasted exactly the same example as above and the result is #VALUE!
I'm not sure why...

Since you don't have Office 365, you have to enter the formula as an array formula, holding CTRL+SHIFT and pressing ENTER. The formula in the formula bar will then look like this:
{=TEXTJOIN(", ",1,IF(B1:B3=MAX(B1:B3),$A1:$A3,""))}

 

[raikks]

Since you don't have Office 365, you have to enter the formula as an array formula, holding CTRL+SHIFT and pressing ENTER. The formula in the formula bar will then look like this:
{=TEXTJOIN(", ",1,IF(B1:B3=MAX(B1:B3),$A1:$A3,""))}

Thanks, works fine now.

 

[raikks]

You need to confirm Peter_SSs's formula with Ctrl+Shift+Enter

or this with normal enter

Book1
	A	B	C
1	apples	3	7
2	bananas	5	4
3	oranges	12	7
4
5		oranges	apples, oranges
Sheet1

Cell Formulas
Range	Formula
B5:C5	B5	=TEXTJOIN(", ",,INDEX(REPT($A1:$A3,B1:B3=MAX(B1:B3)),))

Thank you, i forgot to try the CTRL+SHIFT+ENTER - it does work indeed.

 

[Peter_SSs]

i forgot to try the CTRL+SHIFT+ENTER

Sorry, I forgot to mention that for your version as it is not required in mine.
Thanks Bo-Ry and VBasic2008 for pointing that out.