How do you have a formula link, keep the data from a cell that moves from sorting?

[Asparagus]

In this scenario I have data from A2, B2 & C2 link to F2, it is a concatenate formula which makes F2 cell show "raff 56 w". Column D & E are empty so that my F column does not sort with AB&C columns. I would like to be able to sort the rows for AB&C but wish to keep the content "raff 56 w" in the F2 cell regardless of where it end up in the sort. Meaning if "raff 56 w" in now moved down to row A13, B13 & C13, I wish Cell F2 to pull the information from there. Basically following where ever it sorts and maintaining the link. Is that possible and if so may someone tell me how it is done please?

thanks so much 

[#2 · Accepted solution]

@Asparagus 

That is just the way Excel works - sorting a range does not update references to cells in the range from cells outside that range. A workaround would be to add a column with unique identifiers, and to use INDEX/MATCH, XLOOKUP or VLOOKUP to retrieve values associated with a specific identifier.

The formula in G2 is

=INDEX($B$2:$B$5,MATCH(F2,$A$2:$A$5,0))&" "&INDEX($C$2:$C$5,MATCH(F2,$A$2:$A$5,0))&" "&INDEX($D$2:$D$5,MATCH(F2,$A$2:$A$5,0))

Because we look up the unique index 1, the result will remain the same when the range in columns A to D is sorted.

[#3]

That is so clever, thank you so much, this is more than perfect!

[#4]

@Asparagus 

As variant

=TEXTJOIN(" ",1,INDEX(B2:D5,MATCH(F2,A2:A5,0),0))