Microsoft Excel: How to Create IF ISNA Formulas For Your Spreadsheets

PDF download Download Article
Become an Excel pro by learning how to make your own IF ISNA formulas
PDF download Download Article

Are you struggling with creating IF ISNA formulas in Excel? While it may seem a little confusing, once you learn the basics of IF statements and the ISNA function, you can easily create sophisticated IF ISNA formulas for your spreadsheets. Keep reading for the step-by-step instructions on how to construct these useful formulas.

Things You Should Know

  • ISNA is a function that asks Excel to print if a cell contains a #N/A error or not, but is usually used with other functions.
  • The most popular function to use with ISNA is VLOOKUP, which looks through a specified array of columns for a value.
  • With an IF ISNA formula, you can print custom Boolean messages if the formula returns a value or an #N/A result.

Steps

PDF download Download Article
  1. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/7\/7a\/If-Isna-Step-1.jpg\/v4-460px-If-Isna-Step-1.jpg","bigUrl":"\/images\/thumb\/7\/7a\/If-Isna-Step-1.jpg\/v4-728px-If-Isna-Step-1.jpg","smallWidth":460,"smallHeight":348,"bigWidth":728,"bigHeight":550,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    In Excel , you can use the ISNA function to create an IF formula that will return custom Boolean values instead of the typical #N/A value. [1]
  2. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/1\/16\/If-Isna-Step-2.jpg\/v4-460px-If-Isna-Step-2.jpg","bigUrl":"\/images\/thumb\/1\/16\/If-Isna-Step-2.jpg\/v4-728px-If-Isna-Step-2.jpg","smallWidth":460,"smallHeight":348,"bigWidth":728,"bigHeight":550,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    In the formula bar , type =IF(ISNA .
    • The syntax of an IF statement is =IF(logical_test, true_value, false_value) . A logical test is something that is either true or false. If the IF function is true, it will execute the true_value. If the function is false, it will execute the_false value.
    • IF functions can have nested functions as their arguments to allow you to create sophisticated spreadsheets.
    Advertisement
  3. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/e\/ef\/If-Isna-Step-3.jpg\/v4-460px-If-Isna-Step-3.jpg","bigUrl":"\/images\/thumb\/e\/ef\/If-Isna-Step-3.jpg\/v4-728px-If-Isna-Step-3.jpg","smallWidth":460,"smallHeight":341,"bigWidth":728,"bigHeight":540,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    One of the most common functions to use with ISNA is VLOOKUP , but you can use other functions such as MATCH. VLOOKUP is an Excel function that searches a vertical selection for a value you specify, and returns the matching value.
    • In this article, we'll teach you how to use VLOOKUP with ISNA to create an IF statement.
  4. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/4\/4a\/If-Isna-Step-4.jpg\/v4-460px-If-Isna-Step-4.jpg","bigUrl":"\/images\/thumb\/4\/4a\/If-Isna-Step-4.jpg\/v4-728px-If-Isna-Step-4.jpg","smallWidth":460,"smallHeight":343,"bigWidth":728,"bigHeight":543,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    The syntax for a proper VLOOKUP function is VLOOKUP(value, table_array, col_index_number, true/false) . For example, if you're writing a VLOOKUP formula that searches a fixed two-column wide table array for a student's name and returns a value from the second column (which test they failed), you could write VLOOKUP(A1, $B$1:$C$5, 2, FALSE) (values will differ for your project). Note that the $ signs create an absolute reference, so you can use the formula in other cells without the table array reference shifting.
    • Value: This is the value that should be looked up. It can be a static value, or it can be a cell reference.
    • Table_array: A reference to a range of cells you want VLOOKUP to search through.
    • Col_index_number: The column number that includes the information you want returned. The leftmost column in the table array is 1.
    • True/false: A value that tells the formula whether you're looking for an approximate (true) or exact (false) match. If you omit this value, the function defaults to "true."
  5. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/2\/2c\/If-Isna-Step-5.jpg\/v4-460px-If-Isna-Step-5.jpg","bigUrl":"\/images\/thumb\/2\/2c\/If-Isna-Step-5.jpg\/v4-728px-If-Isna-Step-5.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    Your formula should now look like =IF(ISNA(VLOOKUP(A1, $B$1:$C$5, 2, FALSE)) . Be mindful of closing parentheses, or your IF statement won't work properly.
  6. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/6\/6e\/If-Isna-Step-6.jpg\/v4-460px-If-Isna-Step-6.jpg","bigUrl":"\/images\/thumb\/6\/6e\/If-Isna-Step-6.jpg\/v4-728px-If-Isna-Step-6.jpg","smallWidth":460,"smallHeight":342,"bigWidth":728,"bigHeight":542,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    An IF statement will return one of two messages: one message if the function is true, and one message if the function is false, which is known as a Boolean. For an IF ISNA formula, the true_value is your error message and the false_value is your non-error message.
    • If you want the function to print "No failed tests" for a true_value and "Failed" for a false_value, you would write the following function: =IF(ISNA(VLOOKUP(A1, $B$1:$C$5, 2, FALSE)), "No failed tests", "Failed") . Ensure there are quotes around the messages so Excel will print the correct message.
    • If you want the function to print "No failed tests" for a true_value and the name of the failed test for a false_value, you would write the following function: =IF(ISNA(VLOOKUP(A1, $B$1:$C$5, 2, FALSE)), "No failed tests", VLOOKUP(A1, $B$1:$C$5, 2, FALSE)) . You can nest formulas with IF statements, and in this example, you would repeat the VLOOKUP formula you wrote earlier to print the name of the test that the student failed.
  7. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/5\/55\/If-Isna-Step-7.jpg\/v4-460px-If-Isna-Step-7.jpg","bigUrl":"\/images\/thumb\/5\/55\/If-Isna-Step-7.jpg\/v4-728px-If-Isna-Step-7.jpg","smallWidth":460,"smallHeight":343,"bigWidth":728,"bigHeight":543,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    If you get an error once you've completed your IF ISNA formula or it's not returning the correct value, go through your formula to ensure you've referenced the correct cells and that all parentheses are closed.
  8. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/6\/6a\/If-Isna-Step-8.jpg\/v4-460px-If-Isna-Step-8.jpg","bigUrl":"\/images\/thumb\/6\/6a\/If-Isna-Step-8.jpg\/v4-728px-If-Isna-Step-8.jpg","smallWidth":460,"smallHeight":347,"bigWidth":728,"bigHeight":549,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    Once you've completed your formula, you can easily add it to vertically adjacent cells by dragging the handle in the bottom-right corner of the cell.
    • Cell references will adjust as you drag unless you include a $ sign to denote an absolute reference.
    9. Advertisement

Expert Q&A

Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Tips

      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Submit
      Thanks for submitting a tip for review!

      You Might Also Like

      How to
      Recover a Corrupt Excel File
      How to Merge Cells in Microsoft Excel: A Quick Guide
      Easily Create a Drop-Down List in Microsoft Excel: Setup & Customization
      How to Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
      How to
      Unprotect an Excel Sheet
      How to Create a Graph in Excel
      How to Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
      How to
      Link Sheets in Excel
      How to
      Create a Mortgage Calculator With Microsoft Excel
      How to
      Make a Spreadsheet in Excel
      How to
      Insert Pictures in Excel That Automatically Size to Fit Cells
      How to Unhide Multiple Rows in Microsoft Excel
      How to Password Protect a Microsoft Excel Spreadsheet
      How to Create an Inventory List in Microsoft Excel: Step-by-Step Guide
      Advertisement

      References

      1. ↑ https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665

      About This Article

      Thanks to all authors for creating a page that has been read 2,202 times.

      Is this article up to date?

      Advertisement