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?

Answers

  • Matt Johnson
    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

    Matt Johnson

    Sevan Technology

    Smartsheet PLATINUM Partner

  • Mike B
    Mike B ✭✭✭✭
    edited 07/18/23

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

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

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭
    edited 07/18/23

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!