OK I am going a bit nuts here but have found alot of very helpful information to date on this forum so here goes:
I have a spreadsheet that consolidates scorecard results from 3 different team tabs into a single sheet. The format of the team tabs are identical in structure and I am using the following INDEX/MATCH statement:
=INDEX(Team1Mgr!$D$2:$D$200,MATCH($A3&$B3&$C$2,Team1Mgr!$AA$2:$AA$200))
I am comparing a concatenated field on the COMBINED TEAM tab which essentially equals teamnameFY15month (ex: TEAM1FY15APR) to a field range on the managers tab (Team1Mgr) with the desired result value for the month noted in column D of the Team managers tab.
Now in my formula, I have a mix of three different managers reporting results so my formula while consistent in format has the rows for the managers interspersed across 25 rows. I have been careful to ensure the INDEX/MATCH formula pointer has been modified for the correct manager tab
ROW 1: TeamMgr1 =INDEX(Team1Mgr!$D$2:$D$200,MATCH($A3&$B3&$C$2,Team1Mgr!$AA$2:$AA$200))
ROW 2: TeamMgr3 =INDEX(Team3Mgr!$D$2:$D$200,MATCH($A3&$B3&$C$2,Team3Mgr!$AA$2:$AA$200))
ROW 3: TeamMgr2 =INDEX(Team2Mgr!$D$2:$D$200,MATCH($A3&$B3&$C$2,Team2Mgr!$AA$2:$AA$200))
ROW 4: TeamMgr3 =INDEX(Team3Mgr!$D$2:$D$200,MATCH($A3&$B3&$C$2,Team3Mgr!$AA$2:$AA$200))
.
.
.
.
ROW 11-12: TeamMgr1 =INDEX(Team1Mgr!$D$2:$D$200,MATCH($A3&$B3&$C$2,Team1Mgr!$AA$2:$AA$200))
and so on down through the 25 different teams
What's happening is the values are resolving as expected for TeamMgr1's teams throughout the document; however, the rows for the other team managers are not returning any data at all. I have traced the formulas and they are returning TRUE/FALSE values other than 1 - (104, 130, etc.)
I am guessing this may be a forest for the trees problem but any assistance would be nice !
Bookmarks