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:

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

=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

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

HELPFUL RESOURCE:

 

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error 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

Share on Google+

Google+

Related Posts

Named Ranges with Vlookup Formula What does it do? Searches for a value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array. Formula breakdown: =VLOOKUP(lookup_value, table_array, col_index_num, ) What it means: =VLOOKUP...
VLOOKUP Multiple Columns What does it do? Searches for a value in the first column of a table array and returns the sum of values in the same row from other columns (to the right) in the table array. Formula breakdown: {=SUM(VLOOKUP(lookup_value, table_array, {col_index_num1,col_index_num2}, ))}...
IFERROR Function: Introduction   What does it do? It returns a value that you set if a formula has an error Formula breakdown: =IFERROR(Value,Value if Error) What it means: =IFERROR(The Formula,What do you want to show if The Formula has an error?) If you have a calculation tha...
VLOOKUP with Multiple Criteria in Excel ‘VLOOKUP’ is now so common that everyone who has ever been working with Excel has heard of it. Not necessarily because they know how to use it, but because some savvy Excel-user always talks about it at the office. Advanced users have seen the use of adding more than one...