how can i conditionally move text? Probably IF/THEN

[Bertramus]

I have text in different columns essentially data is spread and I want to consolidate (dropping blanks).  I have column A with numbers that tell me which other columns have text.  For example

• A=1 means there is text in D while E and F are blank. 
• A=2  means there is text in E while D and F are blank
• A=3 means there is text in F while D and E are blank

I want a move any text that exists for D, E, or F into column M.  There is never a situation where there is text in two of D, E, or F.

I think IF(A2=1,???, IF(A2=2, ????,IF(A2=3,????,"NA")) should work, but I am not sure what would replace the ???? to move the text.

Thanks in advance.

[#2 · Accepted solution]

@Bertramus 

Since on;y one of the cells in columns D to F will be filled, you can use the following formula in M2:

=CONCATENATE(D2,E2,F2)

or if you have Excel in Office 2019 or Microsoft 365:

=CONCAT(D2:F2)

Fill down.

[#3]

@Bertramus 

Your convention for the values in column A also makes it possible to look up the data up using INDEX

=INDEX(Data, {1;2;3}, A)

How you generate the row numbers as whether you return the result row by row or as a single array will be influenced by the version of Excel you are developing for.

[#4]

Thanks! this worked.

[#5]

Thanks! a new function for me.