Return to blank #N/A in Excel vlookup

Question

How do I return #N/A to Blank in VLOOKUP in Excel below formula i used

=(VLOOKUP(@$E$7:$E$105,'2025 Material'!$A$2:$E$21,5,FALSE)/VLOOKUP(@$E$7:$E$105,'2025 Material'!$A$2:$E$21,4,FALSE))*H7

Answer 1

Wrap the formula in IFNA (or IFERROR) so that #N/A is replaced with a blank string:

=IFNA(
    (VLOOKUP(@$E$7:$E$105,'2025 Material'!$A$2:$E$21,5,FALSE)
    /VLOOKUP(@$E$7:$E$105,'2025 Material'!$A$2:$E$21,4,FALSE))
    *H7,
    ""
)

Explanation:

• IFNA(value, value_if_na) returns value_if_na only when the result is #N/A.
• Here value is the whole VLOOKUP expression, and value_if_na is "" (blank).
• For any non-#N/A result, the calculated value is returned as normal.

If using IFERROR instead of IFNA and want to catch all error types, use:

=IFERROR(
    (VLOOKUP(@$E$7:$E$105,'2025 Material'!$A$2:$E$21,5,FALSE)
    /VLOOKUP(@$E$7:$E$105,'2025 Material'!$A$2:$E$21,4,FALSE))
    *H7,
    ""
)

---

References:

• How to correct a #N/A error
• IFNA function