I've finally worked out the formula to work, which is below

=IF(ISNA(MATCH(A2,StaffNo,0)),"Crew",INDEX(StaffGr ade,MATCH(A2,StaffNo,0)))"

However I am now trying to place the formula into some code so that it will
copy down, my formula is now what is below but I'm getting a Epected End
Statement


ActiveCell.Formula =
"=IF(ISNA(MATCH(A2,StaffNo,0)),"Crew",INDEX(StaffG rade,MATCH(A2,StaffNo,0)))
"











"John" wrote in message
...
I have the following formula which returns the value Staff or Mgr,
depending
on what each persons StaffNo is attributed under StaffGrade

=INDEX(StaffGrade,MATCH(A6,StaffNo,0))

My problem is that if the Staff number in my column does not exist ithin
StaffNo the formula above returns a #N/A, I want a work around this that
if
the StaffNo does not exists that the value returned will be Staff - is
that
possible?

Thanks

John,

Your might Also find this useful:
=CALC(INDEX(StaffGrade,MATCH(A2,StaffNo,0)),"Crew" )


' Short & Fast: CALC(Expected, If_Error)
Function Calc(Expected, Optional If_Error)
Calc = Expected
If IsError(Expected) Then Calc = If_Error
End Function


Ola

Presumably the quotes. Try

ActiveCell.Formula =
"=IF(ISNA(MATCH(A2,StaffNo,0)),""Crew"",INDEX(Staf fGrade,MATCH(A2,StaffNo,0)
))"


--
HTH

Bob Phillips

"John" wrote in message
...
I've finally worked out the formula to work, which is below


=IF(ISNA(MATCH(A2,StaffNo,0)),"Crew",INDEX(StaffGr ade,MATCH(A2,StaffNo,0)))"

However I am now trying to place the formula into some code so that it
will
copy down, my formula is now what is below but I'm getting a Epected End
Statement


ActiveCell.Formula =

"=IF(ISNA(MATCH(A2,StaffNo,0)),"Crew",INDEX(StaffG rade,MATCH(A2,StaffNo,0)))
"











"John" wrote in message
...
I have the following formula which returns the value Staff or Mgr,
depending
on what each persons StaffNo is attributed under StaffGrade

=INDEX(StaffGrade,MATCH(A6,StaffNo,0))

My problem is that if the Staff number in my column does not exist ithin
StaffNo the formula above returns a #N/A, I want a work around this that
if
the StaffNo does not exists that the value returned will be Staff - is
that
possible?

Thanks