Link data from one sheet to another.

How do I link data to another sheet using INDEX/MATCH, if that the best practice to copy selected fields to another sheet?

Example:

Source Sheet Columns: PK#,Vendor,FY

Destination Sheet Columns: PK#,Vendor,FY

How do I use INDEX and MATCH to copy the data?

Find more posts tagged with

Accepted answers

All comments

Matt Johnson

Hi @Mike B

You need a unique identifier on both sheets. Maybe Project Name or something. Once you get that on both sheets, here's a formula you could use:

=INDEX({PK#}, MATCH([Project Name]@row, {Project Name}, 0))

The "PK#" is a cross sheet reference looking at just the PK# column on the source sheet (its good to rename your ranges in formulas).

The 2nd "Project Name" in the formula is a cross sheet reference looking at just the Project Name column on the source sheet.

Then do a similar formula for the Vendor column and FY column. Doing column formulas as much as possible is a good idea too.

The other option is Datamesh if your plan has that. Datamesh has the added benefit of auto-adding the Unique Identifier to the destination sheet once it gets added to the source sheet.

I hope that helps.

Matt

Mike B

Thanks Matt, I'm getting the following error: #UNPARSEABLE

=INDEX({1. LOAs Range 2}, MATCH([PK #], {1. LOAs Range 2}, 0))

Peggy Parchert

Hello @Mike B

Try this formula in the Destination sheet, Vendor column (updating column names to match yours):

=INDEX({Source sheet Vendor}, MATCH([PK# (destination sheet)]@row, {Source sheet PK#}, 0))

I attempted to create a sheet to show you a visual.

You could cell-link the PK# columns (from Source to Destination) as Matt stated, you need a unique identifier.

Hope this helps.

Peggy

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.