Need Excel2007 Formula Index, partial match

Hello,
I have 2 worksheets that looks like this: (Windows XP / Internet Explorer 7.0/ Excel 2007)
Sheet1: Site Information
ROWS COLUMNS
A(Site) B(City) C(C Manager) D(CM Cell#)
R2 016-125 Jonston =Frank Jones =(999) 999-9999
R3 016-138 Jacksonville =Frank Jones =(999) 999-9999
R4 020-100 Hampstead = Katie Lang =(910) 999-9001
R5 020-135 Jacksonville =Katie Lang =(910) 999-9001
R6 030-150 Holly Ridge =George Mikes =(910) 999-9002
R7 030-155 Maple Hill =George Mikes =(910) 999-9002
R8 030-201 Apple City =George Mikes =(910) 999-9002
R9 030-635 Radon =George Mikes =(910) 999-9002
R10 042-001 Wilton =Anthony Tie = (910) 999-9003
R11 174-361 Jones =Rod Thing =(910) 999-9004
R12 174-367 Jones =Sam Smith =(910) 999-9004
R13 174-420 Hampstead =Rod Thing =(910) 999-9005
R14 174-520 Jacksonville =Sam Smith =(910) 999-9006
R15 168-010 Borsch =Todd James =(910) 999-9007
etc.....
Sheet2: C Managers
ROWS COLUMNS
A(Partial Site/Site) B(C Manager) C(CM Cell#)
R2 016 Frank Jones (999) 999-9999
R3 020 Katie Lang (910) 999-9001
R4 030 George Mikes (910) 999-9002
R5 042 Anthony Tie (910) 999-9003
R6 174-361 Rod Thing (910) 999-9004
R7 174-420 Rod Thing (910) 999-9004
R8 174-367 Sam Smith (910) 999-9004
R9 174-520 Sam Smith (910) 999-9004
R10 168 Todd James (910) 999-9007

What I need is a formula for Sheet1: Column C(C Manager)and D(CM Cell#) to pull in the CM Names and Numbers from Sheet2. I need Column C (Sheet1) to have a formula to look at Cell A2 (Sheet1) to find a partial match of Cell A2(Sheet2), if finds the partial match to put the corresponding CM data. I put the correct answers in Sheet1 Column C and D. I have tried many different types of formulas but none come back with correct data. I even made sure both Column As are formatted text, not number
=INDEX(Sheet2!$B2:$B$10,IF(ISNA(VLOOKUP(VALUE((LEFT(A2,3)-1)),Sheet2!$A$2:$A$10,3,FALSE)),"N.A.",VLOOKUP(VALUE ((LEFT(A2,3)-1)),Sheet2!$A$2:$A$10,2,FALSE))) Answer I get is #VALUE!

=INDEX(Sheet2!$B$2:$B$10,SMALL(IF(ISNUMBER(SEARCH(Sheet2!$A$2:$A$10,$A$2)),ROW($A$2:$A$15)-ROW($A2)+0),ROWS($C$2:C2))) I get Frank Jones for C2, #NUM! for C3-C15..