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

Name	Project	Week 1	Week 2	Week 3	Week 4
BOB	ABC	1	0.5		1
BOB	DEF			1
JANE	DEF	1		1

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]

Name	Project	Week 1	Week 2	Week 3	Week 4
BOB	ABC	1	0.5		1		bob	week 3	DEF
BOB	DEF			1
JANE	DEF	1		1

<tbody>
</tbody>

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.