Index Match and returning the *right* value

[orangebloss]

I have a spreadsheet that is used as a resource planner and I am trying to plot who is working on what in a graphical format (bit like within Project Server - but that's a whole other conversation).

The data is set up as below and I want to return the project name where the value is e.g. Bob in week 3 should return project DEF

[table="width: 500"]
[tr]
[td]Name [/td]
[td]Project[/td]
[td]Week 1[/td]
[td]Week 2[/td]

[td]Week 3[/td]
[td]Week 4[/td]

[/tr]
[tr]
[td]BOB[/td]
[td]ABC[/td]
[td]1[/td]
[td]0.5[/td]
[td][/td]
[td]1[/td]


[/tr]
[tr]
[td]BOB[/td]
[td]DEF[/td]
[td][/td]
[td][/td]
[td]1[/td]
[td][/td]

[/tr]
[tr]
[td]JANE[/td]
[td]DEF[/td]
[td]1[/td]
[td][/td]
[td]1[/td]
[td][/td]

[/tr]
[/table]

The formula I have so far is as follows - which sort of works but will return project ABC even for week 3. Is there a way for it to find where the number is and then return the project name?

=IF(SUMIF(RNAME1,RNAME,'Resource Planner'!P:P)>0,INDEX(PROJECTCODE,MATCH(RNAME,RNAME1,0),MATCH(I$1,Table1[[#Headers],[17/01]])),"")

 

[Aladin Akyurek]

[TABLE="class: grid, width: 640"]
<tbody>[TR]
[TD="width: 64, bgcolor: #FFFFFF"]Name[/TD]
[TD="width: 64, bgcolor: #FFFFFF"]Project[/TD]
[TD="width: 64, bgcolor: #FFFFFF"]Week 1[/TD]
[TD="width: 64, bgcolor: #FFFFFF"]Week 2[/TD]
[TD="width: 64, bgcolor: #FFFFFF"]Week 3[/TD]
[TD="width: 64, bgcolor: #FFFFFF"]Week 4[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FFFFFF"]BOB[/TD]
[TD="width: 64, bgcolor: #FFFFFF"]ABC[/TD]
[TD="width: 64, bgcolor: #FFFFFF"]1[/TD]
[TD="width: 64, bgcolor: #FFFFFF"]0.5[/TD]
[TD="width: 64, bgcolor: #FFFFFF"][/TD]
[TD="width: 64, bgcolor: #FFFFFF"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]bob[/TD]
[TD="bgcolor: transparent"]week 3[/TD]
[TD="bgcolor: transparent"]DEF[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FFFFFF"]BOB[/TD]
[TD="width: 64, bgcolor: #FFFFFF"]DEF[/TD]
[TD="width: 64, bgcolor: #FFFFFF"][/TD]
[TD="width: 64, bgcolor: #FFFFFF"][/TD]
[TD="width: 64, bgcolor: #FFFFFF"]1[/TD]
[TD="width: 64, bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FFFFFF"]JANE[/TD]
[TD="width: 64, bgcolor: #FFFFFF"]DEF[/TD]
[TD="width: 64, bgcolor: #FFFFFF"]1[/TD]
[TD="width: 64, bgcolor: #FFFFFF"][/TD]
[TD="width: 64, bgcolor: #FFFFFF"]1[/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

In j2 control+shift+enter, not just enter:

=INDEX($B$2:$B$4,MATCH(H2,IF(ISNUMBER(INDEX($C$2:$F$4,0,MATCH(I2,$C$1:$F$1,0))),$A$2:$A$4),0))

 

[orangebloss]

Thank you so much!!! it took a bit of shuffling and playing but it worked!!! *phew*

 

[Aladin Akyurek]

Thank you so much!!! it took a bit of shuffling and playing but it worked!!! *phew*

You are welcome. Thanks for the update.