Data flow resulting in No Match when source sheet row is moved

Amy Swan
Amy Swan ✭✭✭✭
edited 04/03/23 in Formulas and Functions

I have tried VLookup, Index, Match, and Index Collect formulas on my destination sheet, however, when a row from the source sheet is moved to an archive sheet, all the data disappears from the destination sheet resulting in "No Match". Is there a way to keep the data in the destination sheet?

The scenario is Human resources has the source sheet with some sensitive info and Employee health needs the list of names and a few columns too. If the employee is termed, HR moves this to an archived employee sheet, but then the data is gone from the employee health file destination sheet. Any suggestions on keeping the data in the destination sheet?

=IFERROR(INDEX(COLLECT({Test MF 1 Employee Name}, {Test MF 1 Row ID}, [Row ID]@row), 1), "No Match Found")


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!