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.
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.