Link data from one sheet to another.

Mike B

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?

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