How to Fix #N/A Errors in Excel Formulas like VLOOKUP


Microsoft Excel might return an error whenever you enter a price or strive to carry out an motion that it fails to perceive. There are a number of kinds of errors, and every error is related to particular kinds of errors you may need made.

The #N/A error is an ordinary Excel error. It seems whenever you’ve referenced information incorrectly. For instance, referenced information that doesn’t exist or exists exterior of the lookup desk, made a spelling error in the lookup worth, or added an additional character in the lookup worth (a comma, apostrophe, or perhaps a area character).

Since the error happens whenever you’ve incorrectly referenced a lookup worth, it’s mostly related to lookup capabilities like LOOKUP, VLOOKUP, HLOOKUP, and the MATCH operate. Let’s take a look at the explanations, an instance, and a few fixes for the #N/A error.

How to Fix #N/A Errors in Excel Formulas like VLOOKUP

Reasons for #N/A Error

Following are the explanations that may trigger a #N/A error in your worksheet:

  • You’ve misspelled a lookup worth (or inserted an additional area character)
  • You’ve misspelled a price in the lookup desk (or inserted an additional area character)
  • The lookup vary has been entered incorrectly into the formulation
  • You’ve used a unique information sort for the lookup worth than the one which exists in the lookup desk (i.e., used textual content as an alternative of numbers)
  • The lookup worth you entered was not discovered in the lookup desk

Example of #N/A Error

Let’s use the VLOOKUP function for example to perceive the way you may find yourself with a #N/A error after utilizing Excel capabilities like LOOKUP, HLOOKUP, or MATCH since they share an analogous syntax construction.

For occasion, say you’ve gotten a protracted record of workers and their bonuses listed in an Excel workbook. 

You use the VLOOKUP formulation, enter a related [lookup_value] for which you insert a cell reference (cell D4), outline the [table_array] (A2:B7), and outline [col_index_num] (2). 

For the ultimate argument referred to as the [range_lookup], it’s best to use 1 (or TRUE) to instruct Excel to acquire an actual match. Setting it to 2 (or FALSE) will instruct Excel to search for an approximate match, which can provide you an incorrect output.

Suppose you’ve arrange a formulation for acquiring bonuses for a choose few workers, however you misspell the lookup worth. You’ll find yourself with a #N/A error as a result of Excel gained’t find a way to discover an actual match for the worth in the lookup desk.

How to Fix #N/A Errors in Excel Formulas like VLOOKUP

So, what should you do to repair this error? 

How to Fix #N/A Error

There are a number of methods for troubleshooting the #N/A error, however the fixes can primarily be categorized into two approaches:

  1. Correcting the inputs
  2. Trapping the error

Correcting the inputs

Ideally, it’s best to determine the reason for the error utilizing the explanations listed beforehand in this tutorial. Fixing the trigger will be certain that you’re not simply eliminating the error but in addition getting the right output.

You ought to begin by utilizing the explanations listed in this information as a guidelines. Doing it will show you how to discover the wrong enter that you just want to repair to eradicate the error. For instance, it could possibly be a misspelled worth, an additional area character, or values with an incorrect information sort in the lookup desk.

Trapping the error

Alternatively, if you would like to simply eradicate errors out of your worksheet with out bothering with individually checking for errors, you should use a number of Excel formulation. Some capabilities have been created particularly to lure errors, whereas others can assist you assemble a logical syntax utilizing a number of capabilities to eradicate errors.

You can lure the #N/A error utilizing one of many following capabilities:

  • IFERROR Function
  • IFNA Function
  • A Combination of ISERROR Function and IF Function
  • TRIM Function

1. IFERROR Function

The IFERROR operate was created with the only goal of fixing the output for a cell that returns an error.

Using the IFERROR operate permits you to enter a particular worth that you really want a cell to present as an alternative of an error. For occasion, if in case you have a #N/A error in cell E2 when utilizing VLOOKUP, you possibly can nest the complete formulation into an IFERROR operate, like so:

IFERROR(VLOOKUP(E4,B2:C7,2,1),“Employee not found”

If the VLOOKUP operate outcomes in an error, it’ll mechanically show the textual content string “Employees not found” as an alternative of the error.

How to Fix #N/A Errors in Excel Formulas like VLOOKUP

You may use an empty string by merely inserting two citation marks (“”) if you would like to show a clean cell when the formulation returns an error.

Note that the IFERROR operate works for all errors. So, as an example, if the formulation you’ve nested contained in the IFERROR operate returns a #DIV error, IFERROR will nonetheless lure the error and return the worth in the final argument.

2. IFNA Function

The IFNA operate is a extra particular model of the IFERROR operate however works precisely the identical means. The solely distinction between the 2 capabilities is that the IFERROR operate traps all errors, whereas the IFNA operate solely traps #N/A errors.

For occasion, the next formulation will work if in case you have a VLOOKUP #N/A error, however not for a #VALUE error:

IFNA(VLOOKUP(E4,B2:C7,2,1),“Employee not found”

How to Fix #N/A Errors in Excel Formulas like VLOOKUP

3. A Combination of the ISERROR Function and the IF Function

Another means to lure an error is to use the ISERROR operate together with the IF operate. It primarily works like the IFERROR operate, in that it depends on the ISERROR operate to detect an error, and the IF operate to render output based mostly on a logical take a look at.

The mixture works with all errors like the IFERROR operate, not simply the #N/A operate. Here’s an instance of what the syntax will look like when trapping an Excel VLOOKUP #N/A error with the IF and ISERROR capabilities:

=IF(ISERROR(VLOOKUP(E4,B2:C7,2,1)),VLOOKUP(E4,B2:C8,2,1),”Employee not discovered”)

How to Fix #N/A Errors in Excel Formulas like VLOOKUP

4. TRIM Function

We mentioned earlier {that a} area character inserted inadvertently in the lookup worth may end up in a #N/A error. However, if in case you have a protracted record of lookup values already populated into your worksheet, you should use the TRIM operate as an alternative of eradicating the area character from every lookup worth individually.

First, create one other column to trim main and trailing areas in the names utilizing the TRIM operate:

How to Fix #N/A Errors in Excel Formulas like VLOOKUP

Then, use the brand new column of names because the lookup values in the VLOOKUP operate.

Fix #N/A Error in Macros

There’s no particular formulation or shortcut you should use to repair #N/A errors in a macro. Since you doubtless added a number of capabilities into your macro whenever you created it, you’ll want to verify the arguments used for every operate and confirm in the event that they’re right to repair an #N/A error in a maco.

#N/A Errors Fixed

Fixing #N/A errors isn’t that tough when you perceive what causes them. If you’re not too involved in regards to the output and simply don’t desire a formulation to outcome in an error, you should use capabilities like IFERROR and IFNA to simply deal with the #N/A error.