Try this

Range("P1").FormulaR1C1 =
"=INDEX([FXAppl.xls]Sheet1!R1C3:R100C3,MATCH(LEFT(Sheet3!RC[-15],4)&""*"",[FXAppl.xls]Sheet1!R1C2:R100C2,0))"

Range("P1").AutoFill Destination:=Range("P1:P40"), Type:=xlFillDefault

Note the top 2 lines have wrapped and are a single line. Adjust the P1:P40
to the actual range you want to fill

Mike

<ike

"franciz" wrote:

Hi

How can I input this formula into col P via a macro

=INDEX([FXAppl.xls]Sheet1!$C$1:$C$100,MATCH(LEFT(Sheet3!A1,4)&"*",[FXAppl.xls]Sheet1!$B$1:$B$100,0))

thanks

regards, francis