The VLOOKUP formula in Microsoft Excel is one of the most efficient ways to find data in spreadsheets. Along with the concatenate formula, I have found it to be one of the more useful tools in Excel. It can save an incredible amount of time when compared to manually searching for cell data, and has the added benefit of being repeatable and accurate.
But if the VLOOKUP formula can’t find the information that it is looking for, it will display an error in the form of #N/A. This can be problematic, especially if the appearance of your data is important. Fortunately you can make a minor alteration to your VLOOKUP formula to display a “)” instead of the #N/A error message.
How to Modify the VLOOKUP Formula in Excel 2013 to Display a Zero Instead of #N/A
The steps below will assume that you already have an existing VLOOKUP formula in place in your spreadsheet, but that you would like it to display a “0” instead of #N/A. The formula will display #NA when it does not find the information that it is looking for. By following the steps below, that will be replaced with a “0”.
Step 1: Open the spreadsheet containing the #N/A value that you want to replace.
Step 2: Select a cell containing the formula that you want to change.
Step 3: Modify the existing VLOOKUP formula to include the IFERROR information. This involves adding the phrase “IFERROR(” to the beginning of the formula, and the string “, 0)” to the end of the formula. For example, if your formula before was:
=VLOOKUP(A2, ‘Data Source’!A$:N$6, 14, FALSE)
Then you would modify it to be:
=IFERROR(VLOOKUP(A2, ‘Data Source’!A$:N$6, 14, FALSE), 0)
Step 4: Copy and paste the new formula into the cells where you want to display a “0” instead of #N/A.
Note that you can choose to display an string of characters that you would like. It doesn’t have to be a 0. For example, if you were using the VLOOKUP formula to insert an address, then you could make the formula =IFERROR(XX, YY:ZZ, AA, FALSE), “NO ADDRESS”). That would show the phrase “NO ADDRESS” instead of a 0 when Excel couldn’t find the data it wanted.
Another helpful formula you can use is called CONCATENATE. This article will show you some ways that you can use that to combine data from multiple cells.
|
Disclaimer: Most of the pages on the internet include affiliate links, including some on this site.
