Since you want to work with a abbreviated name as lookup value...

=LOOKUP(C1649,'CIS DETAILS'!$A$2:$A$1000,'CIS
> DETAILS'!$C$2:$C$1000)

=INDEX('CIS DETAILS'!$C$2:$C$1000,MATCH(C1649&"*",'CIS
DETAILS'!$A$2:$A$1000,0))

wbiggchiefy wrote:
>
> "wbiggchiefy" wrote:
>
>
>>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.
>
>
>
> Also have just noticed that if I enter the full name that is in the
> worksheet I am pulling the info from that the formula works - but would like
> to not have to input the full name (first 8-10 characters should be enough).
>
> Chiefy.