# How to use the NA function

**What is the NA function?**

The NA() function returns the error value #N/A meaning "value is not available". Use the NA() function to fill empty cells to avoid including empty cells in your calculations. Use the ISNA or IFNA function to identify #N/A errors.

**What is the #N/A error value?**

The #N/A error occurs when a value is not available for a formula or found in a given cell range, for example in the VLOOKUPÂ orÂ MATCHÂ functions.

The NA function lets you return the #N/A error in any formula you like, you can for instance use the IF function and a logical expression to determine when the error should occur.

**Related #N/A functions**

Excel Function | Description |
---|---|

NA() | Returns the #N/A error value |

IFNA(value, value_if_na) | Returns value_if_na if value is #N/A, otherwise returns value |

ISNA(value) | Returns TRUE if value is #N/A, FALSE otherwise |

ISERR(value) | Returns TRUE if value is any error except #N/A, otherwise FALSE |

ISERROR(value) | Returns TRUE if value is any error value, FALSE otherwise |

IFERROR(value, value_if_error) | Returns value_if_error if value is any error, otherwise returns value |

### NA function Syntax

NA()

### NA function Arguments

The NA() function has no arguments.

### NA function example

This example demonstrates how to use the NA function. The image above shows the NA function in cell B3, the output is the #N/A error.

Formula in cell B3:

### How to enter the NA function in all empty cells at once?

Here is how to enter the NA() function in all empty cells in a given cell range. I am going to show you how to enter the NA() function in all empty cells in cell range C3:C8 in this example based on the image above:

- Select cell range C3:C12
- Press function key F5, a dialog box appears.
- Press with left mouse button on the "Special.." button, another dialog box appears.
- Press with left mouse button on "Blanks" to select it.
- Press with left mouse button on the "OK" button.

The image shows all empty cells selected.

- Press with left mouse button on in the "Formula bar".

- Type =NA()
- Press and hold the CTRL key.
- Press Enter once.
- Release the CTRL key.

The image above displays the final result, all empty cells are now populated with a #N/A error.

### NA function and VLOOKUP

This example shows how to return the #N/A error if a VLOOKUP function returns an empty value.

Formula in cell F3:

=IF(VLOOKUP(E3,B3:C12,2,FALSE)="",NA(),VLOOKUP(E3,B3:C12,2,FALSE))

### Explaining formula in cell F3

#### Step 1 - Evaluate VLOOKUP

The VLOOKUP function lets you search the leftmost column for a value and return another value on the same row in a column you specify.

Function syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

VLOOKUP(E3,B3:C12,2,FALSE)

returns "", however, Excel shows 0 (zero) meaning the cell is empty.

#### Step 2 - Compare output to nothing ""

The equal sign is a logical operator that lets you compare values. This example shows how to check if the result of the VLOOKUP function is empty.

VLOOKUP(E3,B3:C12,2,FALSE)=""

becomes

""=""

and returns TRUE.

**What is a Boolean value?**

A Boolean value in Excel is a value that can only be TRUE or FALSE. It represents binary logic and is the result of a logical expression using logical operators or a result of a few Excel functions.

Mastering Boolean logic and logical expressions is key to manipulating data and controlling workflow in Excel.

#### Step 3 - Perform logic based on outcome

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

Function syntax: IF(logical_test, [value_if_true], [value_if_false])

IF(VLOOKUP(E3,B3:C12,2,FALSE)="",NA(),VLOOKUP(E3,B3:C12,2,FALSE))

becomes

IF(TRUE,NA(),VLOOKUP(E3,B3:C12,2,FALSE))

and "#N/A" is returned.

### 'NA' function examples

Table of Contents AVERAGE ignore blanks Average - ignore blanks and errors Average - ignore blanks in non-contiguous cells Weighted […]

Bill Truax asks: Hello Oscar, I am building a spreadsheet for tracking calls for my local fire department.Â I have a […]

### Functions in 'Information' category

The NA function function is one of 19 functions in the 'Information' category.

## How to comment

How to add a formula to your comment<code>Insert your formula here.</code>

Convert less than and larger than signsUse html character entities instead of less than and larger than signs.

< becomes < and > becomes >

How to add VBA code to your comment[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

How to add a picture to your comment:Upload picture to postimage.org or imgur

Paste image link to your comment.

Contact OscarYou can contact me through this contact form