VBA to add a column with a VLOOKUP formula referencing another sheet.

Occasional Contributor


The VLOOKUP works but returns #N/A for all non matches. IFERROR results in blank cells. 




                     Dim lastRow As Long
                         lastRow = Cells(Rows.Count, 2).End(xlUp).Row
                            Range("AF2:AF" & lastRow).FormulaR1C1 = "=IF(AND(RC[-9]>350, RC[-11]>25, RC[-11]<90), IF(COUNTIF(C[-15], RC[-15])>1, IF(LARGE(C[-31],1)=RC[-31], ""Duplicate Check"", """"),""""),"""")"
                            Range("AF2:AF" & lastRow).Value = Range("AF2:AF" & lastRow).Formula
                            Range("AG2:AG" & lastRow).FormulaR1C1 = "=VLOOKUP(RC[-19],New Sheet[[Name]:[Exists]],5,0)"





4 Replies
The final argument of VLOOKUP is supposed to be either FALSE (only exact match) or TRUE (non-exact, closest match in a sorted table). Your formula has "3", which is probably treated as TRUE.
Thanks, I have managed to get it working, I now face the issue of all the non-matches show as #N/A, any suggestions on a clean way around it? I have tried iferror and that kills the whole formula.
best response confirmed by Lindsy (Occasional Contributor)


What is New Sheet? Table names cannot contain spaces...

IFERROR works like this when you specify it in VBA:

                            Range("AG2:AG" & lastRow).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-19],New_Sheet[[Name]:[Exists]],5,FALSE),"""")"
Thank you, the sheet name as correct (no spaces, just changed for privacy) It was the double quotation marks I was doing wrong. Thank you so much!!!