I am using Excel Online and use an XLOOKUP formula that populates data from one sheet to another. I have three sheets I am working with:

  1. Responses (this is connected to a Microsoft Form and populates when someone completes the form)
  2. Tracking (this is where I have the XLOOKUP formula, pasted below)
  3. Archive (this is where I am storing data from the responses sheet that have been worked on)


=XLOOKUP(A54, 'Responses'!C3:C9,'Responses'!D3:D9)

The problem I am running into is that I am also using Power Automate to archive data from 'Responses' into an 'Archive' sheet. When data is moved from 'Responses' to 'Archive', I lose data in my 'Tracking' sheet where the formula lives. Ideally, I'd like to add another XLOOKUP formula so that when data is sent to the 'Archive' sheet, the data in my 'Tracking' sheet stays (i.e. it's performing the same search but in a different sheet).

Any help on this front would be much appreciated!