INDEX and MATCH across two sheets: a detailed explanation

Brett Wyrick

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.

• INDEX: https://help.smartsheet.com/function/index
• MATCH: https://help.smartsheet.com/function/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.

Brett Wyrick

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.

Brett Wyrick

Create some references on the 'new' sheet to the 'original' sheet

So, first things first, let's create some References.

Go to the "Companies_Public" Sheet.

Right click any cell, and press "Manage References".

This will bring up a pop-up that says "Sheet Reference Manager". Press the "+ Create" button at the top right.

Under "Search for a data source" on the next screen, type in "Companies_Private". Select that sheet. You'll see a snippet of what the sheet looks like within this page:

Select the "Corp" column by clicking the column here, and then rename the "Sheet reference name" to something simple, like "Corp_Column".

Repeat this step for each column ("Stock_Column", "Website_Column"). Make sure you press "Insert Reference" after each time.

---

Link in Cells from 'original' sheet

You'll want to link in the Cell values for the "Corp" value for each row. The "Corp" value is the Primary value for the row. (for more on Primary values, see this link).

To do that, you can right click the cell, then select "Link from Cell in Other Sheet", then find the applicable cell. You can select a range of cells, so for this exercise, we'll be selecting the Corp column range from the "Companies_Private" sheet:

(please note that this will likely link the first 50 rows of the 'original' sheet - you'll need to delete rows that aren't needed once you're through with this setup, if you want)

This "Corp" value is going to take the role of our "Identifier" for our INDEX MATCH Functions below. For new rows, we'll cover that later. This is taking care of existing rows.

--------

INDEX MATCH Functions!

We're finally here. Here's how to use INDEX MATCH across several sheets.

Now that you have References set up, now you can run the INDEX MATCH function.

Here's how INDEX MATCH works, in a nutshell:

=INDEX([Range of data to be displayed from], MATCH([Identifier], [Range to look for Identifier], [sort option]), [optional column index])

The Identifier is simply something that you can utilize to match a cell value from one sheet to another.

For this Identifier, you should use a cell value that is always unique (otherwise, if there are duplicate values, this formula will take from the first value it can find).

Here's how the INDEX MATCH pair function works:

• Use the first portion of the INDEX formula to set the range of data you want to display.
• Use the MATCH in the second part of the INDEX formula to designate what row to pull the data from.
• The third portion of the INDEX formula is optional. Use this to specify which column to pull the data from if the first portion of the INDEX formula covers multiple columns. For how we've set this up, you won't need to worry about that.

--------

Formulas for the columns

So, for our "Companies_Public" sheet, here's the formulas for the "Stock" and "Website" columns:

Stock column formula:

=INDEX({Stock_Column}, MATCH(Corp@row, {Corp_Column}, 0))

Website column formula

=INDEX({Website_Column}, MATCH(Corp@row, {Corp_Column}, 0))

For each of these, you'll want to add the formula to the top row, right click the cell then select "Convert to column formula". This adds the formula to the entire column.

To break this down, here's how the first formula works:

• Indexes the referenced "Stock_Column" range from the "Companies_Private" sheet (which will ultimately return the value we're looking for)
• Then finds the row number that matches the name from the "Corp" column of the "Companies_Public" sheet by searching through the "Corp_Column" range of the "Companies_Private" sheet.

Same goes for the formula for the "Website" column.

Basically, what this does is matches the name from the "Corp" field of the row, finds the adjacent value for "Stock", and displays the value of "Stock" from the original sheet.

To test this, you can simply add in the names willy-nilly to see if it'll pull up the correct values for the other columns, and it ought to never give you the error "#NO-MATCH" as long as the value exists. Example of testing:

I added "(Public)" to each column to make it clear that this is from the "Companies_Public" sheet.

Daniel Gill 97

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!

Daniel Gill 97

https://community.smartsheet.com/discussion/comment/310662#Comment_310662

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

Brett Wyrick

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!

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!

lm56

Hi @Brett Wyrick - I am very new to Smartsheet and wondering if I could use the index match function to do the following -

In my Test column on the Packaging Sheet - if the printer in the Packaging Sheet matches the printer in the Printer Lead Time Sheet, pull the number from the Total Number of Days column from the Printer Lead Time Sheet

Chrisbrown

In this guide, you will learn how to

 Use only Index function in Google Sheets

 Use only Match function in Google Sheets

 Use both Index Match in Google Sheets

Index Match formula can be a great alternative for Vlookup and Hlookup in Google Sheets.

SEBERCAW

I am very new to smartsheet, but can I use this to update cells on the "public" sheet?

Or are you saying that you cannot update just one changed cell?

Stefan

https://community.smartsheet.com/discussion/comment/337078#Comment_337078

Hi @SEBERCAW

you could use cell links to do that.

Cell Linking | Smartsheet Learning Center

Hope this helps

Stefan

SEBERCAW

I found the correct formula, but if there is no date it has an #INVALID VALUE. I tried fixing it with IFERROR, but that does not work. Everything works except when I add the IFERROR and the opening and closing parentheses.

=IFERROR(INDEX(COLLECT({EEO/HARRASSEMENT}, {NAME}, NAME@row, {EEO/HARRASSEMENT}, <>""), 1))

Stefan

Hi @SEBERCAW,

if everything else is working, then I see one unnecessary parentheses in the end, which is needed at the end of your INDEX formula:

=IFERROR(INDEX(COLLECT({EEO/HARRASSEMENT}, {NAME}, NAME@row, {EEO/HARRASSEMENT}, <>"")), 1)

Also your formula is built to show a 1 if an error is thrown. A 1 would only work, if the column with your formula is e.g. of the type checkmark. Otherwise you need to put the message to be shown in quotation marks:

=IFERROR(INDEX(COLLECT({EEO/HARRASSEMENT}, {NAME}, NAME@row, {EEO/HARRASSEMENT}, <>"")), "No value present")

Hope this helps.

Stefan

SEBERCAW

Thanks, this helped.

Edith Orenstein

Hi @Brett Wyrick and everyone,

I am novice at Smartsheet. Can I use Index/Match to bring data I need to a 400 line Smartsheet from a 2,000 line Smartsheet, if the 400 line Smartsheet is a subset of the 2,000 line Smartsheet? I read somewhere that Index/Match has a 100 line max? TIA for any info/advice

Andrée Starå

Hi @Edith Orenstein

I hope you're well and safe!

Yes, that should work just fine. (There isn't a limit of 100 lines, but there is a limit of 100 distinct references)

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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!