Issues with IFNA

[rollingzep]

I am trying to execute this VLookup but getting Application- Defined Or Object defined error
<code>
ws.Range("BE2:BE" & ws.Cells(Rows.Count, "AG").End(xlUp).Row).FormulaR1C1 = "=IF(ISNA(VLOOKUP(""*""&RC[-24]&""*"",HQLA!C2:C4,4,0)),"Not Found", VLOOKUP(""*""&RC[-24]&""*"",HQLA!C1:C4,4,0))"
</code>

I am trying to Lookup C2 if in case of #N/A or C1 if it is a match.

Alternatively, is it possible to just have an IF condition to look up C1 or C2?

 

[rollingzep]

I modified my code to
ws.Range("BE2:BE" & ws.Cells(Rows.Count, "AG").End(xlUp).Row).FormulaR1C1 = "=IF(ISNA(VLOOKUP(""*""&RC[-24]&""*"",HQLA!C2:C4,4,0)),""Not Found"", VLOOKUP(""*""&RC[-24]&""*"",HQLA!C1:C4,4,0))"

and it returns Not Found in place of #N/A

aIs it possible to have a MID function inside the VLookup?

as in VLOOKUP(""*""&RC[-24]&""*"",HQLA!MID(C2, 3,9):C4,4,0))?