INDEX and MATCH across two sheets: a detailed explanation

Brett WyrickBrett Wyrick ✭✭✭
edited 11/09/21 in Best Practice
11/08/21 Edited 11/09/21

Hello everyone, would love some feedback on this tutorial.

I was answering a user's question regarding INDEX and MATCH (thread post here), and thought it would be beneficial to share my example to the greater Smartsheet community of how to easily utilize INDEX and MATCH functions to reference cells from another sheet by using a shared "identifier" value.

I couldn't find any sources of a true step-by-step tutorial of how to create sheet references to a second sheet range, then show how to utilize INDEX/MATCH to get the info from those ranges.

This tutorial includes a step-by-step example of how to create one sheet as a "data source" sheet, and a second sheet that's used for a more "public" sort of view. The idea here is for the data to have a "home" on the source sheet and a select set of columns available on the other sheet (useful for sharing when you don't want to share all the data from a sheet).

----

Documentation so you can get savvy:

Here's the documentation from Smartsheet for both Index and Match.

Below, I'll try and explain a layman's terms way to do INDEX MATCH - I'd recommend using INDEX/MATCH functions rather than VLOOKUP because INDEX/MATCH functions are more helpful if your data moves around (i.e., if one row gets moved, VLOOKUP can "break").

For your use case, you'll be utilizing what Smartsheet refers to as "Cross-sheet references". You can read about Cross Sheet Formulas (and there's a video) here: https://help.smartsheet.com/learning-track/smartsheet-advanced/cross-sheet-formulas

----

Due to limits on characters for posting, I'll add my solution as several responses to this discussion post.

If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

🧔 Brett Wyrick; your friendly neighborhood freelance consultant & Smartsheet helper.

❓ Need some more help? Want to automate systems outside of Smartsheet to connect to your Smartsheet? Want to automate your sheets to talk to one another easier? Other questions? Send me an email or connect with me on LinkedIn.‎

Comments

  • Example: A Tale of Two Sheets

    For this example, we'll have 2 sheets that have the same info on each, minus one column on the "Public" sheet.

    • The first sheet we will call "Companies_Private"
    • The second sheet will be named "Companies_Public".

    Here's the data within the "Companies_Private" sheet. Bold & italicized text = Column Name.

    ____________________________________________________________

    Companies_Private Sheet:

    copy/paste data in your own sheet, if you'd like:

    Columns:

    Corp‎ │Stock │ Website │CEO

    3M ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ MMM ‎ ‎ ‎ ‎ 3m.com ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎Mike Roman

    Adobe ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ADBE ‎ ‎ ‎ ‎ ‎adobe.com ‎ ‎ ‎ ‎ ‎Shantanu Narayen

    Alphabet ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ GOOG ‎ ‎ ‎ ‎ google.com ‎ ‎ ‎ ‎ ‎Sundar Pichai

    Amazon ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎AMZN ‎ ‎ ‎ ‎ amazon.com ‎ ‎ ‎Andy Jassy

    Apple ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ AAPL ‎ ‎ ‎ ‎ apple.com ‎ ‎ ‎ ‎ ‎ Tim Cook

    Best Buy ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎BBY ‎ ‎ ‎ ‎ ‎ ‎bestbuy.com ‎ ‎ ‎ ‎Corie Barry

    Etsy ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ETSY ‎ ‎ ‎ ‎ ‎Etsy.com ‎ ‎ ‎ ‎ ‎ ‎ ‎ Josh Silverman

    Hewlett Packard ‎ ‎ ‎ HP ‎ ‎ ‎ ‎ ‎ ‎ HP.com ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ Enrique Lores

    ____________________________________________________________

    So, for our second Sheet, which we will name "Companies_Public", we just need a sheet with the same columns, minus one (for this example, we'll be removing the "CEO" column - let's say that you didn't want particular users to see that column).

    ____________________________________________________________

    Companies_Public Sheet:

    Corp‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ Stock ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎Website ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎

    ____________________________________________________________

    So, to get this to work, you'll need to utilize INDEX MATCH functions and create some "References" to the "Private" sheet from the "Public" sheet.

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    🧔 Brett Wyrick; your friendly neighborhood freelance consultant & Smartsheet helper.

    ❓ Need some more help? Want to automate systems outside of Smartsheet to connect to your Smartsheet? Want to automate your sheets to talk to one another easier? Other questions? Send me an email or connect with me on LinkedIn.‎

  • Hi Brett,

    Very new to Smartsheet.

    I want to use this technique to copy columns over from one Smartsheet to another as the 'copy rows' option, takes over more information than i would like.

    Is this possible, and if so, can you explain the index match in simpler detail for me?

    Thanks!

  • I think where it says for new added rows, if you could cover that it would answer my question!

  • Brett WyrickBrett Wyrick ✭✭✭
    edited 11/11/21

    Howdy there @Daniel Gill 97 , thanks for commenting.

    As far as the "copy rows" automation is concerned, there's no way to simply copy over particular cell values from one "sheet" to another sheet. In other words, you can't pick and choose which columns you want. It's either copy everything or copy nothing.

    So, you could copy the row, then move all the columns you don't want to the far right in your sheet, then select them and hide them. That's not a great solution, but it would work.

    Others have asked your exact question before, but there's not a straightforward way to do this solely within Smartsheet: https://community.smartsheet.com/discussion/69496/copy-rows-automation-only-certain-columns

    Here's a handy tutorial that has helped me when I was learning INDEX / MATCH: https://www.youtube.com/watch?v=cabxWacMhKQ. It shows how to utilize INDEX/MATCH on a sheet and how to use it referencing another sheet.

    ----

    But....

    There are ways to do what you're wanting to do, however. You just need to use the Smartsheet API, if you're interested. Basically, the API allows users to do customized actions like what you're desiring and a whole lot more (add a column to every sheet, hide columns automatically every night, etc.)

    I utilize Microsoft's Power Automate (which is similar to Zapier, Automate.io, etc.) to do custom automations like you're desiring. I could walk you through how to do that if you're interested.

    I'm currently working up a Smartsheet consulting freelancing business, so I'm looking for some testimonials. So I could set this up for free.

    However, you would be required to have a subscription to one of those automation services in order for me to set it up. If your org uses Microsoft 365, you may already have access to Power Automate through your existing license - check here and see if you can utilize it: https://powerautomate.microsoft.com/en-us/

    Let me know!

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    🧔 Brett Wyrick; your friendly neighborhood freelance consultant & Smartsheet helper.

    ❓ Need some more help? Want to automate systems outside of Smartsheet to connect to your Smartsheet? Want to automate your sheets to talk to one another easier? Other questions? Send me an email or connect with me on LinkedIn.‎

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi @Daniel Gill 97

    I hope you're well and safe!

    To add to Brett's excellent advice/answer.

    You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

    Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet and then copy/move the row from that sheet to the main destination sheet.

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.