More than once my work colleagues got irritated by seeing the dreaded #NA or #N/A text breaking their beautiful tables and worksheets. Younger ones didn’t even know that the #NA acronym stands for, so I had to explain it time and again.
What If We Could Replace #NA with Not Applicable or Other Relevant Message? Welcome to IF ISNA & VLOOKUP!
In this article I’ll explain how to use the IF ISNA and VLOOKUP functions in Excel to speed up your data analyses and to prevent errors from pestering your reports.
Using Excel functions like IF ISNA and VLOOKUP can help you analyze data more efficiently and accurately. But learning how to apply these powerful functions takes some practice.
This comprehensive step-by-step guide teaches you how to use IFISNA and VLOOKUP in Excel with examples to help you master these functions.
An Overview of IF ISNA and VLOOKUP
IF ISNA and VLOOKUP are two Excel functions that serve different but complementary purposes:
– IFISNA checks whether a value is an error and returns an alternative value if so. This allows you to catch and handle errors in formulas gracefully.
– VLOOKUP looks up specified data in the leftmost column of a table and returns the value from another column. It’s useful for matching data across tables and performing analytics.
Combining IF ISNA and VLOOKUP can help catch lookup errors and prevent incorrect data from propagating through your models. We’ll look at examples of how to do this below.
Using IF ISNA to Catch and Handle Formula Errors
The IF ISNA function tests if a value is an error and returns the specified value if true. The syntax is:
=IFISNA(value, value_if_na)
Where:
– `value` is the cell, formula, or value to test
– `value_if_na` is the result returned if value is an error
For example, let’s say you have a VLOOKUP formula that may occasionally fail with #N/A errors due to improper matches.
Rather than displaying #N/A in results, you can nest IFISNA around the formula to catch errors and display a custom message:
=IFISNA(VLOOKUP(A2,C:D,2,FALSE),"No match found")
Now if VLOOKUP returns #N/A, you’ll see the friendly “No match found” message instead.
## How to Use VLOOKUP to Retrieve Data
The VLOOKUP function searches vertically through the leftmost column of a table for a matching value and returns data from another column. The syntax is:
=VLOOKUP(lookup_value, table_array, column_index_num, [range_lookup])
Breaking down the arguments:
– `lookup_value` is the value to search for in the leftmost column of the table
– `table_array` is the full data table to search through
– `column_index_num` is the column number in the table containing the return value
– `range_lookup` is an optional boolean to control exact or approximate matches
For example, you have a table with employee IDs, names, and departments structured like:
| Employee ID | Name | Department |
| 473990 | Jane Smith | Marketing |
| 583049 | Bob Lee | Engineering |
To look up Jane Smith’s department by her employee ID, you would use:
=VLOOKUP(473990,A1:C4,3,FALSE)
This returns “Marketing” by looking for 473990 in column A and retrieving the value from 3 columns over in column C.
Combining IFISNA and VLOOKUP to Control Errors
By combining IFISNA and VLOOKUP, you can perform lookups while gracefully handling any errors or invalid matches.
For example, with our employee data above, you might lookup an invalid ID and want to display a message rather than #N/A.
The formula would be:
=IF(ISNA(VLOOKUP(12345,A1:C4,3,FALSE)),"Employee ID not found")
Since 12345 isn’t a valid ID, VLOOKUP will return #N/A. But IF ISNA catches that and displays the custom error message instead.
This technique helps prevent errors from propagating throughout large models when doing multiple nested lookups.
Examples of IFISNA and VLOOKUP Usage
There are many possible applications for using IFISNA and VLOOKUP together:
- Looking up email addresses from a list by customer ID, handling invalid IDs
- Retrieving product prices by SKU from an online database, catching discontinued items
- Importing tax rates from a state code table, handling invalid state values
- Linking employee information like job title and manager from a roster, handling departed employees
- Fetching shipping costs from a carrier and order value table, handling oversized items
The key is first understanding your data relationships and types of lookups needed. Then utilize IF ISNA and VLOOKUP to efficiently map values between tables and detect any errors.
In Summary
Mastering functions like IF ISNA and VLOOKUP is crucial for advanced Excel users. Learning to combine them unlocks more robust formulas and models.
In this guide we covered:
– Using IF ISNA to catch errors from other formulas
– Performing vertical lookups with VLOOKUP syntax
– Nesting IF ISNA and VLOOKUP to handle invalid lookup values
– Real-world examples of using these functions together
With some practice, you’ll be able to use IF ISNA and VLOOKUP to extract and cross-reference data in your workbooks like an expert.
Meanwhile you may want to take a look at this article about comparing two Excel columns with VLOOKUP. Also, make sure you know how to restrict what your users enter into your data sheets.
Let me know if you have any other questions!
