Can anyone help with using INDEX and MATCH to pull data across from other sheets?

Sarahh

So I tried using the page I have linked below to help me with this but I am running into some issues.

I have created two testing sheets to use until I get the set up right before applying it to my work,, New Sheet2 and New Sheet3. I am wanting to display info from New Sheet2 in New Sheet3. There is one column that will be a unique identifier that links the rows between the two sheets and I want to pull in about 6 values from New Sheet2 to populate cells in New Sheet3.

So far I have created References in New Sheet3 to link to 2 columns in New Sheet2, and then I am using a formula to pull the info in. I have also attached below a snip of the Sheet Reference Manager I have set up in New Sheet 3, the error message I am getting for the formula, and the formula I am using. Please help!

INDEX and MATCH across two sheets: a detailed explanation

Brett Wyrick

Nov 8, 2021

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.

---

View Post

C. Sullivan

The second look up columns should match between sheets, not necessarily the same column name, but the same information. Your return should be the information you want.

The logic looks like this:

=INDEX({Look Up Column}, MATCH([Column to Look Up]@row, {Info to Return}, 0))

So just going by what you have it should be:

=INDEX({Column15}, MATCH(Column15@row, {Detector Assy SN}, 0))

Sarahh

It's now coming up with a #CIRCULAR REFERENCE error instead

Sarahh

Do I need to set up the references on the page I am pulling data from or pulling data into, currently I have the reference set up on the sheet I was to pull data into

C. Sullivan

My apologies, my formula was based on what you had in the picture. The column the first column is the column you're telling Smartsheet to look at on another sheet. When you set up the MATCH portion, the first value is what you're telling it to search for, and the second value is the information you want returned for that cell.

Sarahh

I have managed to fix it now thank you!