I have tried the following IF statement

=IF('Unassigned Players'!E4:E18="Colin",'Unassigned Players'!C4:C18,"")

but that just returns the #VALUE! error.

For information "Colin" exists in 'Unassigned Players'!E7 and I would like to copy the cell from C7