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

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")

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Does the source sheet get sorted, or is it just rows being removed for archiving?

Amy Swan

Just rows are being removed for archiving when the termed column row is checked on the source sheet. The only solution I found is to disable the auto-move row on the source sheet and have employee health (the destination sheet) move the row to archive the employee first and then have the employee moved from the HR source sheet. That seems to be the only way I found to retain the content and avoid "No match". I was hoping to use the term column check to auto-move row to the archive sheet first in the source sheet and then have it show and do the same thing in the employee health sheet when it was index collected. :(

Paul Newcome

Do you happen to have access to either Data Shuttle or Data Mesh?

Amy Swan

No, I don't. Only core product.

Paul Newcome

Are you adding more fields to the destination sheet, or is it all cell linked for informational purposes?

Amy Swan

No I have only about rows linked and another 20 columns of employee health-specific data

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.