Correct a #N/A error

Microsoft Office Excel 2003

previous page next page
  • Click the cell that displays the error, click the button that appears Button image, and then click Trace Error if it appears.
  • Review the possible causes and solutions.

    • Possible causes and solutions

    ShowMissing data, and #N/A or NA() has been entered in its place

    Replace #N/A with new data.

    Note  You can enter #N/A in those cells where data is not yet available. Formulas that refer to those cells will then return #N/A instead of attempting to calculate a value.

    ShowGiving an inappropriate value for the lookup_value argument in the HLOOKUP, LOOKUP, MATCH, or VLOOKUP worksheet function

    Make sure the lookup_value argument is the correct type of value— for example, a value or a cell reference, but not a range reference.

    ShowUsing the VLOOKUP, HLOOKUP, or MATCH worksheet function to locate a value in an unsorted table

    By default, functions that look up information in tables must be sorted in ascending order. However, the VLOOKUP and HLOOKUP worksheet functions contain a range_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 match_type argument. To find an exact match, set the match_type argument to 0.

    ShowUsing an argument in an array formula that is not the same number of rows or columns as the range that contains the array formula

    If the array formula has been entered into multiple cells, make sure the ranges 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 15 rows high (C1:C15) and the formula refers to a range 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).

    ShowOmitting one or more required arguments from a built-in or custom worksheet function

    Enter all arguments in the function.

    ShowUsing a custom worksheet function that is not available

    Make sure the workbook that contains the worksheet function is open and the function is working properly.

    ShowRunning a macro that enters a function that returns #N/A

    Make sure the arguments in the function are correct and in the correct position.

    previous page start next page