While I'm glad to hear you got it working, it wasn't supposed to be so
labour intensive ! Think you got hit with some implementation problems <g

(Link to download a working sample file is provided below)

Let's try it again ..

Select C3:J3 (< the range selection part is important !)

Put in the formula bar (with C3:J3 selected):

=IF(A3="","",IF(ISNA(MATCH(TRIM(A3),$A$4:$A$10,0)) ,"-",VLOOKUP(TRIM(A3),$A$4
:$J$10,{3,4,5,6,7,8,9,10},0)))

(The entire formula above has to be in a single line. You have to correct
the inevitable line breaks/wraps which will be present when you directly
copy the formula from this post and paste it into the formula bar)

After you have corrected the line breaks in the formula, array-enter the
formula,
i.e press CTRL+SHIFT+ENTER (instead of just pressing ENTER)

If you do this correctly, Excel will put curly braces { } around the formula

Every cell within C3:J3 will be filled with the same formula when you
array-enter, but the correct results will be returned within each cell

Here's a link to a sample file with the working implementation above:
http://www.savefile.com/files/5605128
File: Macro_Formula_Help_misc.xls
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----