Show All

Hide All

This error indicates that a value is not available to a function or formula.

Symptom

Excel displays #N/A in one or more cells on a worksheet.

Causes

• Data is missing, and #N/A or NA() has been entered in its place.
• An inappropriate value was given for the lookup_value argument in the HLOOKUP, LOOKUP, MATCH, or VLOOKUP worksheet function.
• The VLOOKUP, HLOOKUP, or MATCH worksheet function was used to locate a value in an unsorted table.
• An array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.) is using an argument that is not the same number of rows or columns as the range that contains the array formula.
• One or more required arguments were omitted from a built-in or custom worksheet function.
• A custom worksheet function that you use is not available.
• A macro that you run enters a function that returns #N/A.

Resolution

• Optionally, if error checking is turned on in Excel, click the button that appears next to the cell that displays the error , click Show Calculation Steps if it appears, and then click the resolution that is appropriate for your data.

   Tip   Review the following resolutions to help determine which option to click.

• If you manually entered #N/A in a cell, replace it with actual data if that data is now available. For example, if you entered #N/A in cells where data is not yet available, formulas that refer to those cells also return #N/A instead of attempting to calculate a value. If you enter a value instead, the error should be resolved in the cells that contain the formulas.
• Make sure that the lookup_value argument (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) that you entered in a HLOOKUP, LOOKUP, MATCH, or VLOOKUP worksheet function is the correct type of value. For example, verify that you entered a value or a cell reference instead of a range reference.

  For information about using the correct arguments with functions, see HLOOKUP function, LOOKUP function, MATCH function, or VLOOKUP function.

• By default, functions that look up information in tables must be sorted in ascending order. However, the VLOOKUP and HLOOKUP worksheet functions contain arange_lookup argument that instructs the function to find an exact match even if the table is not sorted. To find an exact match, set the range_lookup argument to FALSE.

  The MATCH worksheet function contains a match_type argument that specifies the order the list must be sorted in to find a match. If the function cannot find a match, try changing the value of the match_type argument. To find an exact match, set the match_type argument to 0.

• If an array formula has been entered into multiple cells, make sure that the ranges that are referenced by the formula have the same number of rows and columns, or enter the array formula into fewer cells. For example, if the array formula has been entered into a range that is 15 rows high (C1:C15) and the formula refers to a range that is 10 rows high (A1:A10), the range C11:C15 will display #N/A. To correct this error, enter the formula into a smaller range (for example, C1:C10), or change the range to which the formula refers to the same number of rows (for example, A1:A15).

  For more information about working with array formulas, see Guidelines and examples of array formulas.

• Enter all required arguments in the function that returns the error.
• Make sure that the workbook that contains the worksheet function is open and that the function is working properly.
• Make sure that the arguments in the function are correct and are used in the correct position.

• How to remove #n/a error in excel vlookup b/c value is not found?