Find and concatenate random columns from second column to last used third column on every row

New Contributor

Hi all,


I have a file with 100K+ records where values are in multiple columns randomly from Column A to AG

Here is an example but not limited to the columns shown below.


Any help to:

1. Extract all second last column in every row and paste in another new column


102221 return "ACEWS"

102221245 return "HRRecruit"


2. Extract and concat values from columnB to last third column (need to find in every row e.g. ColumnG for RefID "102221245"). Copy and paste in another new column

102221245 return "Participant + Update Projects"

10222 return "Credits Frequently Ask Qns New Approach HSM"


Edit or View is always the last column in every row.


102221245text2 Participant+UpdateProjectsHRRecruitEdit
102221245text3 Participant+UpdateProjectsHRRecruitEdit


Help is greatly appreciated. It's taking me many hours to figure out.


2 Replies


Hello, take a look at my example document. I have implemented your wishes with:


=IFERROR(TEXTJOIN(" ",TRUE,INDEX(A2:I2,SEQUENCE(1,LOOKUP(2,1/--(A2:I2<>""),COLUMN(A2:I2))-3,2))),"")


I hope it fits so far. It doesn't always come out what you said it would. There is still a mistake somewhere.

A more modern version with XLOOKUP and XMATCH:




@dscheikeymany thanks for your help.

I do see the formula having Name? issue after I tried applying the same.

Since it contains unsupported like --, _xlfn, Sequence in your formula.




However manage to get this working: