Array Formula with Multiple Lookup Columns

[Rcaaa]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Table from Task sheet
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]W
[/TD]
[TD]X
[/TD]
[TD]Y
[/TD]
[TD]Z
[/TD]
[TD]AA
[/TD]
[TD]AB
[/TD]
[TD]AC
[/TD]
[TD]AD
[/TD]
[TD]AE
[/TD]
[TD]AH
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]ID
[/TD]
[TD]Project Name
[/TD]
[TD]Task ID
[/TD]
[TD]Task Description
[/TD]
[TD]Task Type
[/TD]
[TD]Start Date
[/TD]
[TD]End Date
[/TD]
[TD]# of Days
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Resource 1
[/TD]
[TD]Resource 1 Contact
[/TD]
[TD]Resource 2
[/TD]
[TD]Resource 2 Contact
[/TD]
[TD]Resource 3
[/TD]
[TD]Resource 3 Contact
[/TD]
[TD]Resource 4
[/TD]
[TD]Resource 4 Contact
[/TD]
[TD]Resource 5
[/TD]
[TD]Resource 5 Contact
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]Project 1
[/TD]
[TD]Task A
[/TD]
[TD]Task a is
[/TD]
[TD]Main
[/TD]
[TD]1/20/17
[/TD]
[TD]1/20/18
[/TD]
[TD]365
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Ana
[/TD]
[TD]Ana@gmail.com
[/TD]
[TD]Rob
[/TD]
[TD]Rob@gmail.com
[/TD]
[TD]Bill
[/TD]
[TD]Bill@gmail.com
[/TD]
[TD]Jan
[/TD]
[TD]Jan@gmail.com
[/TD]
[TD]Sara
[/TD]
[TD]Sara@gmail.com
[/TD]
[/TR]
</tbody>[/TABLE]

Table Array Formula:
=IF(ROWS(C$7:C22)>$C$2,"",INDEX(Tasks!B$3:B$2012,SMALL(IF(Tasks!$U$3:$AH$2012=$B$2,ROW(Tasks!$U$3:$AH$2012
)-ROW(Tasks!$U$3)+1),ROWS(C$7:C22))))

Sheet with Table Array(Resource View)
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Resource
[/TD]
[TD]Count
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Ana
[/TD]
[TD]6
[/TD]
[/TR]
</tbody>[/TABLE]

PROBLEM: I can only pull data that matches with 1 column of resources. I want to be able to have the data fill if the resource name is in any of the resource columns. For example if Ana is in the Resource 2 column for project 1 and resource 3 column for project 4, I want both entries to appear.

 

[Special-K99]

Not saying Ill answer this but your example data suggests there will only be 4 Resources.
Is that true?

 

[Rcaaa]

I would actually have 5 Resources. There should be a scroll bar that shows the rest of the data.
Thanks,
RCAAA

 

[Marcelo Branco]

Say you have this in Tasks (dates as dd/mm/yyyy)

Columns A:F

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

ID​

[/TD]
[TD]

Project Name​

[/TD]
[TD]

Task ID​

[/TD]
[TD]

Task Description​

[/TD]
[TD]

Task Type​

[/TD]
[TD]

Start Date​

[/TD]
[TD]

End Date​

[/TD]
[TD]

# of Days​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

1​

[/TD]
[TD]

Project 1​

[/TD]
[TD]

Task A​

[/TD]
[TD]

Task a is​

[/TD]
[TD]

Main​

[/TD]
[TD]

20/01/2017​

[/TD]
[TD]

20/01/2018​

[/TD]
[TD]

365​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

2​

[/TD]
[TD]

Project 2​

[/TD]
[TD]

Task X​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

3​

[/TD]
[TD]

Project 3​

[/TD]
[TD]

Task y​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

4​

[/TD]
[TD]

Project 4​

[/TD]
[TD]

Task A​

[/TD]
[TD]

Task a is​

[/TD]
[TD]

Main​

[/TD]
[TD]

20/01/2017​

[/TD]
[TD]

20/01/2018​

[/TD]
[TD]

365​

[/TD]
[/TR]
</tbody>[/TABLE]


Columns W:AF

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

W​

[/TD]
[TD="bgcolor: #DCE6F1"]

X​

[/TD]
[TD="bgcolor: #DCE6F1"]

Y​

[/TD]
[TD="bgcolor: #DCE6F1"]

Z​

[/TD]
[TD="bgcolor: #DCE6F1"]

AA​

[/TD]
[TD="bgcolor: #DCE6F1"]

AB​

[/TD]
[TD="bgcolor: #DCE6F1"]

AC​

[/TD]
[TD="bgcolor: #DCE6F1"]

AD​

[/TD]
[TD="bgcolor: #DCE6F1"]

AE​

[/TD]
[TD="bgcolor: #DCE6F1"]

AF​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Resource 1​

[/TD]
[TD]

Resource 1 Contact​

[/TD]
[TD]

Resource 2​

[/TD]
[TD]

Resource 2 Contact​

[/TD]
[TD]

Resource 3​

[/TD]
[TD]

Resource 3 Contact​

[/TD]
[TD]

Resource 4​

[/TD]
[TD]

Resource 4 Contact​

[/TD]
[TD]

Resource 5​

[/TD]
[TD]

Resource 5 Contact​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Ana​

[/TD]
[TD]

Ana@gmail.com​

[/TD]
[TD]

Rob​

[/TD]
[TD]

Rob@gmail.com​

[/TD]
[TD]

Bill​

[/TD]
[TD]

Bill@gmail.com​

[/TD]
[TD]

Jan​

[/TD]
[TD]

Jan@gmail.com​

[/TD]
[TD]

Sara​

[/TD]
[TD]

Sara@gmail.com​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Rob​

[/TD]
[TD]

Rob@gmail.com​

[/TD]
[TD]

Ana​

[/TD]
[TD]

Ana@another.com​

[/TD]
[TD]

Bill​

[/TD]
[TD]

Bill@gmail.com​

[/TD]
[TD]

Jan​

[/TD]
[TD]

Jan@gmail.com​

[/TD]
[TD]

Sara​

[/TD]
[TD]

Sara@gmail.com​

[/TD]
[/TR]
</tbody>[/TABLE]


Try in another sheet

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD]

Resource​

[/TD]
[TD]

Count​

[/TD]
[TD]

Project List​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD][/TD]
[TD]

Ana​

[/TD]
[TD]

2​

[/TD]
[TD]

Project 1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Project 4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in C2
=COUNTIF(Tasks!W:AF,B2)

Array formula in D2 copied down
=IF(C$2>=ROWS(D$2:D2),INDEX(Tasks!B$3:B$2012,SMALL(IF(Tasks!W$3:AF$2012=B$2,ROW(Tasks!W$3:AF$2012)-ROW(Tasks!W$3)+1),ROWS(D$2:D2))),"")
Ctrl+Shift+Enter

Hope this helps

M.

 

[Rcaaa]

M,

You are the best.

Thank you for fixing my problem.

Thanks again,

RCAAA

 

[Marcelo Branco]

You are welcome. Glad to help

M.