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([email protected]=1, INDEX({Team 1 Status},MATCH([Proj ID]@Row,{Team 1 ID},0)), if([email protected]=2,INDEX({Team 2 Status},MATCH([Proj ID]@Row,{Team 2 ID},0)),if([email protected]=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([email protected]=1, INDEX({Team 1 Status},MATCH([Proj ID]@Row,{Team 1 ID},0)), if([email protected]=2,INDEX({Team 2 Status},MATCH([Proj ID]@Row,{Team 2 ID},0)),if([email protected]=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

  • @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