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 How to use the CHOOSECOLS function How to use the CHOOSEROWS function How to use the DROP […]
Table of Contents AVERAGE ignore blanks Average - ignore blanks and errors Average - ignore blanks in non-contiguous cells Weighted […]
This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […]
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 signs
Use 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 Oscar
You can contact me through this contact form