Index, match, countif function

[ebeyert]

Excel 2016
One workbook

Sheet: Project Team Cube
Row D4: D100 contains all resource names. (I created named range: EmployeeNameProjectTeam)
Note: this sheet is a hidden sheet and the same names can appear several times.


Sheet: Contact List & Details
Row C11: C100

What I want: I want to copy all the resource names from the Sheet: Project Team Cube to this sheet, but that the double names are taken away.

For this I have use the following array formula:

=IFERROR(INDEX(EmployeeNameProjectTeam,MATCH(0,COUNTIF('Contact List & Details'!$C$9:C10,EmployeeNameProjectTeam),0)),"")

Which work ok.

The only issue is, that If I remove a name in the Sheet: Contact List & Details by deleting a row, that the name will return in the next row…

So is there a possibility that if I remove names they will not come back.?

Thanks
Ellerd

 

[James006]

Hi,

Is your named range: EmployeeNameProjectTeam ... defined dynamically ...?

HTH

 

[ebeyert]

James006, no the named ranged is not defined dynamically.

 

[James006]

James006, no the named ranged is not defined dynamically.

My guess is that with a dynamically named range ... you should solve your problem ...

See Debra's excellent explanation :

https://www.contextures.com/xlNames01.html

Hope this will help