Return cells in a range based on multiple match criteria?

[Lewzerrrr]

Hi,

I have a weekly planner with tasks that I'm trying to organise.

In sheet1, I have my Tasks in Column B (B3:B48) and then a 3 week task schedule in columns E,F,G with my users who are performing each task in Column B.

Small sample set of data

Tasks	User		Week 31	Week 32	Week 33
Task1	All		User1	User2	User3
Task2	All		User2	User3	User1
Task3	All		User3	User1	User2
Task4	All		All	All	All
Task5	All		User2	User3	User1
Task6	All		User3	User1	User2

<colgroup><col><col span="5"></colgroup><tbody>
</tbody>

And then in Sheet2, I have my week header so Week 31 merged in C1:G1 with my users underneath All, User1, User2.. C2:G2

I need a formula that will return all my tasks for that user but will change when the week changes.

So.. I want it to match the week header in sheet2 (this will change every week but is static across sheet1 E2:E4), and then match the user and return ALL the tasks for that user when dragged down.

Is this possible?

 

[Lewzerrrr]

Correction

So.. I want it to match the week header in sheet2 (this will change every week but is static across sheet1 E2:G2),

 

[Lewzerrrr]

Better explanation.

A

B

C

D

E

F

G

H

I

J

K

L

1

Tasks

User

Week 31

Week 32

Week 33

Week 31

2

Task1

ALL

ALL

ALL

All

User1

User2

User3

3

Task2

User1

User3

User3

Task1

Task2

Task3

Task5

4

Task3

User2

User2

User2

Task4

Task6

Task9

Task8

5

Task4

ALL

ALL

ALL

Task7

6

Task5

User3

User2

User1

7

Task6

User1

User2

User3

Week 32

8

Task7

User1

User2

User3

All

User1

User2

User3

9

Task8

User3

User1

User2

Task1

Task8

Task3

Task2

10

Task9

User2

User3

User2

Task4

Task5

Task9

Task6

Task7

<tbody>
</tbody>

The week 31 table I1:L5 is always going to be there, when I change the week number I want to automatically update the users tasks from the 2nd table D1:F10 (see week 32 table I7:L12).

So essentially in cell I3 - it needs to look up the user in the header, look down the column that has the week header e.g week 31 column D, if it changes to week 32 then column E..

Find the first user and return the task in column A. I then need to drag it down so it will repeat but ignore the first value and so on.. dragging across and doing the same for each user.

Is this possible?