Pinterest Pixel

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... read more

Download Excel Workbook
Bryan
Posted on

Overview

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!

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

I explain how you can do this below:

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

DOWNLOAD EXCEL WORKBOOK

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

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

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.

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

 

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“:

See also  MONTH Formula in Excel

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

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

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!

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

 

Combine VLOOKUP with IFERROR in Excel

 

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!