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

Options

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!



Answers

  • C. Sullivan
    Options

    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
    Options

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

  • Sarahh
    Options

    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
    Options

    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
    Options

    I have managed to fix it now thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!