How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

Ever tried to use the  VLOOKUP Function but it returns a #N/A error?

Do not worry as it is very easy to fix this error when you combine VLOOKUP with IFERROR!

Let’s try it out on these tables!

I explain how you can do this below:

Download excel workbookVLOOKUP-with-iferror.xlsx

STEP 1: Let us use VLOOKUP to get the cost of the item:

=VLOOKUP(E6, $A$6:$C$8, 3, FALSE)

• E6 will give us the value to lookup (Tablet)
• $A$6:$C$8 will highlight the entire table of source data
• 3 as we want the Cost which is in the third column
• FALSE will ensure it’s an exact match

Apply the same formula to the rest of the cells by dragging the lower right corner downwards.

You will see there’s a #N/A value! 

That is because Lamp is not included in the source table.

STEP 2: Let us make it look better with IFERROR!

If we wrap IFERROR around the VLOOKUP formula, it will replace these invalid values with the text that we specify, “Not Found“:

=IFERROR(VLOOKUP(E6, $A$6:$C$8, 3, FALSE), “Not Found”)

Apply the same formula to the rest of the cells by dragging the lower right corner downwards.

And just like that, you now have clean results!

Combine VLOOKUP with IFERROR in Excel

Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.