Pls Help

Lookup function is driving me mad -

I am trying to get LOOKUP function to return a value from cell, say column C
on a worksheet but the error is erroneous (it seems to be returning a value
attached to another entry on the worksheet with the same first 4 letters)

Is there some confine within excel which only 'looks up' the first 4 letters
of a name or am I missing something?

Also sometimes I get values returned to the formula I have used which seem
to be 4-5 rows out of sync.

I am using formula =LOOKUP(C1649,'CIS DETAILS'!$A$2:$A$1000,'CIS
DETAILS'!$C$2:$C$1000)

Thanks in advance.

Chiefy.