EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Information Functions in Excel ISNA Excel Function
 

ISNA Excel Function

Pradeep S
Article byPradeep S
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated August 24, 2023

ISNA Function in Excel

 

 

ISNA in Excel

The IsNA function is one of those functions used to find whether a cell has any error. And this only detects one type of error, #N/A which usually occurs when we try to look up any value from the lookup range and that value is not there. So, in the end, we get #N/A which summarizes that the value we want is “Not Available”, and the IsNA function detects if the process would get #N/A or not by returning the TRUE and FALSE statements.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

ISNA Formula in Excel:

Below is the ISNA Formula in Excel:

ISNA Formula

ISNA Formula in Excel has one critical parameter: i.e., value.

  • Value (compulsory argument) – The value or expression that needs to be teweightis entered manually or defined variables or a cell reference to use instead.

How to Use the ISNA Function in Excel?

ISNA Function in Excel is very simple and easy to use. Let us understand the working of ISNA Function in Excel by some ISNA Formula in Excel examples.

You can download this ISNA Function Excel Template here – ISNA Function Excel Template

Example #1 – ISNA Function

In the below-mentioned table. I have the various error values in the ERROR_VALUES column (column D); here, with the ISNA Function’s help in Excel, I need to find out the #N/A error in column D.

ISNA Example 1-1

Let’s apply the ISNA function in cell “E8”. Select the cell “E8,” where the ISNA function needs to be applied.

ISNA function

Click the insert function button (fx) under the formula toolbar; a dialog box will appear, type the keyword “ISNA” in the search for a function box & under select a category dropdown, select “INFORMATION” so that the ISNA function will appear in select a Function box. Double click on ISNA Function.

ISNA function 1

A dialog box appears where arguments for ISNA Function need to be filled or entered, i.e., =ISNA(value)

Function Argument 1

Value: Here, I have to check whether the value in cell D8 contains the #N/A error. I must mention the cell reference “D8”, i.e., =ISNA(D8).

Function Argument 2

Here, =ISNA(D8) will return TRUE if the value is an #N/A error or return FALSE if it doesn’t contain a #N/A error.

Cell D8 does not contain the #N/A error; it will return FALSE as an output in cell “E8”.

ISNA Formula Result 1

To get the output for the complete dataset, click inside cell E8 to see the cell selected, then Select the cells till E14. Once it got selected, the column range will get selected; click on Ctrl + D so that the ISNA formula is applied to the whole range.

ISNA Formula Result 2

E13 cell returns TRUE value because the D13 cell contains #N/A Error.

ISNA Formula Result 3

Example #2 – ISNA & IF Function with VLOOKUP Function to Avoid #N/A Error

In the below-mentioned table1. I have the list of students in the Student column (Column J) & their scores in the Score column (Column K).

List of students

Table 2 contains a list of student names in the student column (column M); our objective here in this table is to find the student’s score based on his name, i.e., student name (refer to table 1), by using the VLOOKUP Function.

Student score

Before applying a VLOOKUP formula, You should be aware of it. Vertical lookup or VLOOKUP references vertically aligned tables and quickly finds data in relation to the value the user enters.

Let’s apply VLOOKUP Function in cell “N7”. Select the cell “N7” where VLOOKUP Function needs to be applied.

VLOOKUP Function

Click the insert function button (fx) under the formula toolbar; a dialog box will appear, type the keyword “VLOOKUP” in the search for a function box; VLOOKUP Function will appear in the select a Function box. Double click on the VLOOKUP function.

Dialog Box 2

A dialog box appears where arguments for the VLOOKUP function need to be filled or entered.

The syntax for the VLOOKUP function is:

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

  • lookup_value: the value you want to look up, i.e., “M7” or “CINDER”.
  • table_array: range where the lookup value is located, i.e., select table1 range J6:K17.
  • col_index_num: column number in a table array from which the matching value should be returned. The student score in Table 1 is in the second column, i.e., 2.
  • range_lookup: FALSE for an exact match or TRUE for an approximate game. Select 0 or false.

Function Argument 3

=VLOOKUP(M7, J6:K17,2,0) returns the score of cinder, i.e., 77.

ISNA Example 2-6

To get the finalized data for other students, click inside cell N7 to see the cell selected, then Select the cells till N12. Once it got selected, the column range will be selected; click on Ctrl + D so that the VLOOKUP formula is applied to the whole range.

ISNA Example 2-7

Here, the #N/A error is returned in cells N10 & N12 because the Vlookup function fails to find a match to the supplied lookup_value in the reference table range.

ISNA Example 2-8

#N/A error looks odd for a better outlook of a final dataset without a N/A error. ISNA function is used with the IF function in the VLOOKUP function to avoid the #N/A error, where the #N/A error value is replaced with a customized text string.

Suppose I want a customized message, i.e., “Not found” instead of the #N/A error.

Let’s work on this in Table 3; below mentioned ISNA function with IF is used with the VLOOKUP function to avoid the #N/A error.

ISNA Example 2-9

=IF(ISNA(VLOOKUP(P7,J6:K17,2,0)),”Not found”,VLOOKUP(P7,J6:K17,2,0))

Three parts of the formula are

  • =IF(ISNA(VLOOKUP(P7,J6:K17,2,0)),

The first part of the formula means if the function doesn’t find the value of cell P7 in table range J6:K17, then display the second part.

  • “Not found.”

Display “Not found” instead of a #N/A.

  • VLOOKUP(P7,J6:K17,2,0))

If the function finds the value, perform the VLOOKUP formula as usual.

Let’s apply this formula in cell “Q7”.

ISNA Formula with IF Function

The Result will be :

Result of ISNA Formula with IF Function

We need to apply this formula for a whole date range to get the finalized data for other students. Click inside cell “Q7” to see the cell selected; then Select the cells until Q12. Once it gets selected, the column range will get selected; click on Ctrl + D to apply the formula to the whole range.

ISNA Function Result

It will return a customized text string mentioned in the formula, i.e., “Not found” instead of the #N/A error in the cell N10 & N12.

Result of ISNA Function Example 2

Things to Remember about the ISNA Function in Excel

  • ISNA argument value can be a blank (empty cell), text, number, error, logical, or reference value, or a name referring to any of these you want to test.
  • IS functions are significant in formulas for testing the outcome of a calculation. If it is combined with IF logical conditions, it will help out to find the error value.

Recommended Articles

This has been a guide to ISNA in Excel. Here we discuss the ISNA Formula in Excel and How to use ISNA Function in Excel, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles –

  1. LN Excel Function
  2. COMBIN Function in Excel
  3. SUMIF Function in Excel
  4. Excel ISBLANK Function

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download ISNA Function Excel Template

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

EDUCBA

Download ISNA Function Excel Template

EDUCBA

डाउनलोड ISNA Function Excel Template

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW