I see #N/A instead of content in some cells

Cause: One or more arguments is missing or omitted in the function.

Solution:    Enter all argument in the function.

1. Select the cell.

2. On the Formulas tab, under Function, click Formula Builder.

   

3. Edit the formula in the Arguments section of the Formula Builder window.

Cause: The formula uses a custom function that is not available.

Solution:    Make sure that the workbook that contains the custom function is open and that the custom function is working correctly.

1. Select the cell.

2. On the Formulas tab, under Function, click Formula Builder.

   

3. Edit the formula in the Arguments section of the Formula Builder window.

   Note: Some built-in functions that are available in Excel for Windows are not available in Excel for Mac. To learn more, see My function doesn't work.

Cause: An argument in an array formula does not reference the same number of rows or columns as the range that contains the array formula.

Solution:    Adjust the references in the array formula to match, 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).

Cause: An inappropriate value is given for the lookup_value argument in the HLOOKUP, LOOKUP, MATCH, or VLOOKUP function.

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

1. Select the cell.

2. On the Formulas tab, under Function, click Formula Builder.

   

3. Under Description, click More help on this function, and then review the accepted values listed in the lookup_value argument description.

4. Edit the formula in the Arguments section of the Formula Builder window.

Cause: The VLOOKUP, HLOOKUP, or MATCH function is used to locate a value in an unsorted table.

Solution:    Set the range_lookup argument to FALSE.

By default, functions that look up information in tables need to have data that is sorted in ascending order. However, the VLOOKUP and HLOOKUP 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 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.

Cause: #N/A or =NA() has been entered in a referenced cell.

Solution:    Replace #N/A with new data.

You can enter #N/A in those cells where data is not yet available. After you enter #N/A, formulas that refer to those cells display #N/A instead of calculating a value.