How to Find Missing Values in Excel (3 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

In Excel, we often need to look for the presence of a value in a particular dataset so that we can know if a particular value is missing and re-enter it into the dataset. Throughout this article, we will discuss 3 methods for how to find missing values in Excel. In addition to that, we will also learn about a method to compare two lists for missing values.


Download Practice Workbook


3 Easy Ways to Find Missing Values in Excel

In this article, we will talk about three methods to find missing values in Excel. Firstly, we will use a combination of the IF and COUNTIF functions. Secondly, we will use the IF, ISNA, and MATCH functions together to pull off the task. Finally, we will use the IF, ISNA, and VLOOKUP functions in conjecture to find the missing values. We will use the following sample dataset to illustrate the methods.

how to find missing values in excel


1. Using Combination of IF and COUNTIF Functions

The IF function returns a result based on a given condition. The COUNTIF function counts the values from a range or table based on premeditated conditions. In this method, we will combine the two functions to find the missing values in excel.

Steps:

  • Firstly, select the F5 cell and write the following formula,
=IF(COUNTIF(B5:B10,E5),"Found","Missing")
  • Then, hit Enter.

inserting if and countif function to find missing values in excel

  • Consequently, you will find the values that are missing from the Employee ID list.

using combination of if and countif functions to find missing values in excel

🔎 Formula Breakdown:

  • COUNTIF(B5:B10,E5): The COUNTIF function counts the number of cells that satisfy a specified criterion. It returns zero if no cells satisfy the requirement. In this case, the function will go through the range (B5:B10) and look for the value in the E5 If it finds it, then it will return 1. Otherwise, it will return zero.
  • IF(COUNTIF(B5:B10,E5),”Found”,”Missing”): Any non-zero value will be treated as TRUE by the IF function, and zero will be treated as FALSE. In the formula, the COUNTIF function will either return zero or one. If it returns zero, the IF function will treat it as FALSE and will return “Missing”, the second output. And if the COUNTIF function returns one, then the IF function will return “Found”. In this particular case, the COUNTIF function will return 1, and thus the IF function will return “Found”.

Read More: How to Fill Missing Values in Excel (5 Useful Tricks)


2. Combining IF, ISNA, and MATCH Functions

In this example, we will combine three functions, namely the IF, ISNA, and MATCH functions, to get the job done. If a formula returns the #N/A error value, the ISNA function returns TRUE; if not, it returns FALSE. The MATCH function looks for a specific item in a range of cells, finds it, and then returns the item’s position in the range relative to the search criteria.

Steps:

  • Initially, choose cell F5 and enter the following formula:
=IF(ISNA(MATCH(E5,B5:B10)),"Missing","Found")
  • Then, press Enter.

  • As a result, you will discover the missing values from the Employee ID column.

combining if, isna, and match functions to find missing values in excel

🔎 Formula Breakdown:

  • MATCH(E5,B5:B10): The MATCH function goes through a range looking for a particular value. If it finds the value, then it returns the position of the value in numeric value. In this case, the MATCH function will find a match in the range (B5:B10) for the value in the E5 cell and will return a numeric value as its position in the range.
  • ISNA(MATCH(E5,B5:B10)): The ISNA function returns TRUE if a formula returns the #N/A error value; otherwise, it returns FALSE. Here, the MATCH function will return a numeric value and so the ISNA function will assign FALSE to that value.
  • IF(ISNA(MATCH(E5,B5:B10)),”Missing”,”Found”): The IF function evaluates the first argument. If the argument is TRUE then it returns the second argument and if not, it returns the third argument. Here, the ISNA function will return FALSE and so the IF function will return “Found”. This means the value is present in the (B5:B10)

Read More: How to Filter Missing Data in Excel (4 Easy Methods)


Similar Readings


3. Applying IF, ISNA, and VLOOKUP Functions in Conjunction

The VLOOKUP function searches for a value in a range or a table and returns the specified value if it finds the value; otherwise, it returns FALSE. In this example, we will combine it with the IF and ISNA functions to find the missing values.

Steps:

  • To begin with, choose cell F5 and enter the following formula:
=IF(ISNA(VLOOKUP(E5,B5:B10,1,FALSE)),"Missing","Found")
  • Then, press the Enter button.

inserting if, isna and vlookup functions to find missing values in excel

  • Consequently, you will learn what values are missing from the Employee ID column.

applying if, isna, and vlookup functions in conjunction to find missing values in excel

🔎 Formula Breakdown:

  • VLOOKUP(E5,B5:B10,1,FALSE): Here, the VLOOKUP function will look for the value in the E5 cell in the (B5:B10) If it finds the value, then it will return 1, otherwise, it will return FALSE.
  • ISNA(VLOOKUP(E5,B5:B10,1,FALSE)): The ISNA function returns TRUE if a formula returns the #N/A error value; otherwise, it returns FALSE. Here, the VLOOKUP function will return 1 and so the ISNA function will assign FALSE to that value.
  • IF(ISNA(MATCH(E5,B5:B10)),”Missing”,”Found”): The initial argument is assessed by the IF function. If the argument is TRUE then it returns the second argument, and if not, it returns the third argument. Here, the ISNA function will return FALSE, and so the IF function will return “Found”. This means the value is present in the (B5:B10)

Read More: How to Deal with Missing Data in Excel (6 Suitable Ways)


Using FILTER and COUNTIF Functions Together to Compare Two Lists for Missing Values

In this additional method, we will combine the FILTER and COUNTIF functions to compare two lists for missing values. You can use the FILTER function to filter a variety of data according to the criteria that you specify.

Steps:

  • To start with, choose cell F5 and enter the formula below:
=FILTER(B5:B10,COUNTIF(E5:E8,B5:B10)=0)
  • Then, hit Enter.

  • Consequently, you will learn which participant IDs are missing in the passed participants’ list or simply find the participants who failed.

🔎 Formula Breakdown:

  • COUNTIF(E5:E8,B5:B10): Here, the COUNTIF function will count the number of each value in the present in the range (E5:E8). The criteria for counting are the values in the range (B5:B10). For example, it will look for the value 102 in the (E5:E8I range as the value is present in the (B5:B10) r If it finds the value, then it counts as one. If it finds the value multiple times, then it will assign the number of times it has counted the value. On the other hand, if it does not find the value, then it will count as zero. In this case, the value 101 is not present in the (E5:E8) range, thus it will count as zero.
  • FILTER(B5:B10,COUNTIF(E5:E8,B5:B10)=0): The FILTER function will filter out the values from the range B5:B10 that the COUNTIF function will count zero times. In this way, the formula will find the missing values in the Participant (passed) list that is present in the Participant

Read More: How to Compare Two Excel Sheets to Find Missing Data (7 Ways)


Conclusion

In this article, we have learned about some handy ways to find missing values in Excel. These will help the users to find the missing values in their dataset and correct them accordingly.


Related Articles

Adnan Masruf
Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo