Index Match Across Multiple Sheets

Hi Everyone,

Hoping y'all can help as I'm at a complete loss.

I'm trying to pull information into 1 master sheet based on a unique identifier across multiple sheets and it's just not working. I can get it to work for 1 sheet using =INDEX({Status},MATCH([Proj ID]@Row,{Proj ID},0)). Each sheet is for a different team, therefore I created multiple references. I'm wanting to pull the status of a project into the master sheet as it gets updated on the individual team sheet.

Proj ID = unique identifer across all sheets

Team 1 Status, Team 1 Proj ID

Team 2 Status, Team 2 Proj ID

Team 3 Status, Team 3 Proj ID

I tried this formula using the above references =IFERROR(IFERROR(INDEX({Team 1 Status},MATCH([Proj ID]@Row,{Team 1 Proj ID},0)), INDEX({Team 2 Status},MATCH([Proj ID]@Row,{Team 2 ID},0)), INDEX({Team 3 Status},MATCH([Proj ID]@Row, {Team 3 ID},0)) this returned #UNPARSABLE.

I tried =INDEX({Team 1 Status},MATCH([Proj ID]@Row,{Team 1 ID},0)),INDEX({Team 2 Status},MATCH([Proj ID]@Row,{Team 2 ID},0)),INDEX({Team 3 Status},MATCH([Proj ID]@Row,{Team 3 ID},0)) this returned #UNPARSABLE

What am I doing wrong? HELP!

Best Answer

  • MVP OPS
    MVP OPS ✭✭✭✭✭
    Answer ✓

    Hi Alison,

    it looks like you are just missing if statements. If you have a way on your main sheet to identify which team or project you are referencing then you can just add three if statements to each of your formulas.

    =if(team@row=1, INDEX({Team 1 Status},MATCH([Proj ID]@Row,{Team 1 ID},0)), if(team@row=2,INDEX({Team 2 Status},MATCH([Proj ID]@Row,{Team 2 ID},0)),if(team@row=3, INDEX({Team 3 Status},MATCH([Proj ID]@Row,{Team 3 ID},0)))))


    Hope this helps.

    best,

    Brad

    MVP OPS

    best,

    Brad

    www.MVPOPS.com

Answers

  • MVP OPS
    MVP OPS ✭✭✭✭✭
    Answer ✓

    Hi Alison,

    it looks like you are just missing if statements. If you have a way on your main sheet to identify which team or project you are referencing then you can just add three if statements to each of your formulas.

    =if(team@row=1, INDEX({Team 1 Status},MATCH([Proj ID]@Row,{Team 1 ID},0)), if(team@row=2,INDEX({Team 2 Status},MATCH([Proj ID]@Row,{Team 2 ID},0)),if(team@row=3, INDEX({Team 3 Status},MATCH([Proj ID]@Row,{Team 3 ID},0)))))


    Hope this helps.

    best,

    Brad

    MVP OPS

    best,

    Brad

    www.MVPOPS.com

  • AlisonG
    AlisonG ✭✭

    @MVP OPS

    Thank you SO much, that worked perfectly!

  • MVP OPS
    MVP OPS ✭✭✭✭✭

    Great news!

    You are very welcome


    best,

    Brad

    MVP OPS

    best,

    Brad

    www.MVPOPS.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!