Forum Discussion
How do you have a formula link, keep the data from a cell that moves from sorting?
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.
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.
