Try this slightly tweaked version:
=IF(ISNUMBER(MATCH(B4,Staff!$A$2:$A$2000,0)),VLOOK UP(B4,Staff!$A$2:$C$2000,2,0)&" "&VLOOKUP(B4,Staff!$A$2:$C$2000,3,0),"")

Slight tweaks:
a. MATCH range should be a single col: Staff!$A$2:$A$2000
b. Concat between surname & first name now bears a space:" ", re the part:
&" "&
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Steved" wrote:
Hello from Steved ( I'm not thinking straight. )

=IF(ISNUMBER(MATCH(B4,Staff!$A$2:$C$2000,0)),VLOOK UP(B4,Staff!$A$2:$C$2000,2,0)&""&VLOOKUP(B4,Staff! $A$2:$C$2000,3,0),"")

I've a sheet Named Staff it has Employee No, Surname and First names.

On Sheet1 I have the above formula and employee No. in Col B.

My objective is to lookup in Staff the employee from the number ie 10022 in B4
Ok now look in Sheet named Staff find 10022 in column A and then copy the
Surname in Col B and The first Names in Col C to Sheet1

What Have I missed in the above formula please or in my state have I got it
completley wrong.

Thankyou.