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.

**Table of Contents**Expand

## How to Find Missing Values in Excel: 3 Easy Ways

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

**functions. Secondly, we will use the**

*COUNTIF**,*

**IF****and**

*ISNA,**functions together to pull off the task. Finally, we will use the*

**MATCH****functions in conjecture to find the missing values. We will use the following sample dataset to illustrate the methods.**

*IF, ISNA, and VLOOKUP*### 1. Using Combination of IF and COUNTIF Functions

** The IF function** returns a result based on a given condition.

**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.**

*The COUNTIF function***Steps:**

- Firstly, select the
cell and write the following formula,*F5*

`=IF(COUNTIF(B5:B10,E5),"Found","Missing")`

- Then, hit
.*Enter*

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

**🔎 **Formula Breakdown:

**COUNTIF(B5:B10,E5):**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*The COUNTIF function**(*and look for the value in the**B5:B10**)If it finds it, then it will return*E5*. Otherwise, it will return*1*.*zero*Any non-zero value will be treated as**IF(COUNTIF(B5:B10,E5),”Found”,”Missing”):**by the*TRUE*function, and zero will be treated as*IF*. In the formula, the*FALSE*function will either return zero or one. If it returns zero, the*COUNTIF*function will treat it as*IF*and will return*FALSE*, the second output. And if the*“Missing”*function returns one, then the IF function will return**COUNTIF**. In this particular case, the COUNTIF function will return*“Found”*and thus the*1,*function will return*IF*.*“Found”*

**Read More: **How to Fill Missing Values in Excel

### 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

**error value, the**

*#N/A**function returns*

**ISNA****; if not, it returns**

*TRUE***. The**

*FALSE***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.**

*MATCH***Steps:**

- Initially, choose cell
and enter the following formula:*F5*

`=IF(ISNA(MATCH(E5,B5:B10)),"Missing","Found")`

- Then, press
.*Enter*

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

**🔎 **Formula Breakdown:

**MATCH(E5,B5:B10):**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 (*the MATCH function**B5:B10**)*for the value in thecell and will return a numeric value as its position in the range.*E5***ISNA(MATCH(E5,B5:B10)):**returns*The ISNA function*if a formula returns the*TRUE*error value; otherwise, it returns*#N/A*. Here,*FALSE*will return a numeric value and so*the MATCH function*will assign FALSE to that value.*the ISNA function***IF(ISNA(MATCH(E5,B5:B10)),”Missing”,”Found”):**evaluates the first argument. If the argument is*The IF function*then it returns the second argument and if not, it returns the third argument. Here,*TRUE*will return*the ISNA function*and so the IF function will return*FALSE*. This means the value is present in the*“Found”**(***B5:B10**)

**Read More: **How to Filter Missing Data in Excel

### 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

**. In this example, we will combine it with**

*FALSE**to find the missing values.*

**the IF and ISNA functions****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
button.*Enter*

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

**🔎 **Formula Breakdown:

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

**Read More: **How to Deal with Missing Data in Excel

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

In this additional method, we will combine ** the FILTER** and

**to compare two lists for missing values. You can use**

*COUNTIF functions***to filter a variety of data according to the criteria that you specify.**

*the FILTER function***Steps:**

- To start with, choose cell
and enter the formula below:*F5*

`=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,will count the number of each value in the present in the range*the COUNTIF function**(*. The criteria for counting are the values in the range**E5:E8**)*(*. For example, it will look for the value**B5:B10**)in the*102**(*range as the value is present in the**E5:E8**I*(*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**B5:B10**)**r**is not present in the*101**(*range, thus it will count as zero.**E5:E8**)**FILTER(B5:B10,COUNTIF(E5:E8,B5:B10)=0):**will filter out the values from the range*The FILTER function*that*B5:B10*will count zero times. In this way, the formula will find the missing values in the*the COUNTIF function*list that is present in the*Participant (passed)**Participant**.*

**Read More: **How to Compare Two Excel Sheets to Find Missing Data

**Download Practice Workbook**

## 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

- How to Cross Reference in Excel to Find Missing Data
- How to Remove Missing Values in Excel
- How to Find Missing Rows in Excel
- How to Count Missing Values in Excel
- How to Find Missing Values in a List in Excel

**<< Go Back To Missing Values in Excel | Data Cleaning in Excel | Learn Excel**