Forum Discussion
CONCAT question
=IFERROR(INDEX(J2:P2,SMALL(IF(J2:P2<>"",COLUMN($A$1:$G$1)),1)),"")In my first reply unfortunately i didn't consider that the data is in columns J to P. The above formula returns the intended results in my sheet. The COLUMN part is COLUMN($A$1:$G$1) because this evaluates to {1.2.3.4.5.6.7}. In range J2:P2 column J is number 1, column K is number 2 and so on.
=IFERROR(INDEX(J2:P2,SMALL(IF(J2:P2<>"",COLUMN($A$1:$G$1)),1)),"")
The IFERROR part of the formula (highlighted in red) returns an empty cell if there isn't a name in the row.
=INDEX(A2:E2,SMALL(IF(A2:E2<>"",COLUMN($A$1:$E$1)),1))An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
Which version of Excel do you work with? If you have Office 365 or Excel 2021 then there are more possibilities than with e.g. Excel 2013.
OliverScheurich this worked perfectly in my fake spreadsheet so thank you! However when I tried to use the same format but for my real spreadsheet I received this message: #REF! or #NUM!
I changed the formula to:
=INDEX(J2:P2,SMALL(IF(J2:P2<>"",COLUMN($J$1:$P$1)),1))
Do I need to update some of the COLUMN part of the formula?
Here is an example of my real spreadsheet:
=IFERROR(INDEX(J2:P2,SMALL(IF(J2:P2<>"",COLUMN($A$1:$G$1)),1)),"")In my first reply unfortunately i didn't consider that the data is in columns J to P. The above formula returns the intended results in my sheet. The COLUMN part is COLUMN($A$1:$G$1) because this evaluates to {1.2.3.4.5.6.7}. In range J2:P2 column J is number 1, column K is number 2 and so on.
=IFERROR(INDEX(J2:P2,SMALL(IF(J2:P2<>"",COLUMN($A$1:$G$1)),1)),"")
The IFERROR part of the formula (highlighted in red) returns an empty cell if there isn't a name in the row.
- That worked beautifully. Thank you very much for taking the time to help me with this today.