Index Match Across Multiple Sheets

AlisonG

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!

MVP OPS

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

MVP OPS

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

AlisonG

@MVP OPS

Thank you SO much, that worked perfectly!

MVP OPS

Great news!

You are very welcome

best,

Brad

MVP OPS