INDEX and MATCH across two sheets: a detailed explanation
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.