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)),)) |
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,"")) |
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)),)) |
ADVERTISEMENT
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:Unfortunately, it doesn't work - copy pasted exactly the same example as above and the result is #VALUE!
I'm not sure why...
{=TEXTJOIN(", ",1,IF(B1:B3=MAX(B1:B3),$A1:$A3,""))}
Thanks, works fine now.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,""))}
Thank you, i forgot to try the CTRL+SHIFT+ENTER - it does work indeed.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)),))
Sorry, I forgot to mention that for your version as it is not required in mine.i forgot to try the CTRL+SHIFT+ENTER