Find missing values in Excel

In this article, we will learn about how to look up missing values from the table using the formula in excel.

For instance, we have a list of values and we need confirmation regarding the whereabouts of the value in list. For this, we will be considering the situation and suggest some formulas to do the same. We will be using different function operation depending on the ease of the table_array.

The following function will be in use. So get to know a little about them before using it.

  1. IF function
  2. COUNTIF function
  3. ISNA function
  4. VLOOKUP function
  5. MATCH function

We will construct a formula out of it. Firstly, the lookup value is searched in the particular column of the table array. Then the matched values will give us the confirmation using the IF function. The IF function returns the confirmation using the values "Is there" & "Missing".

First Method: Use of COUNTIF and IF function

Use the generic formula

= IF ( COUNTIF ( list , cell_value), "Is there" , "Missing" )

Explanation:

  • COUNTIF function keeps the count of cell_value in the list and returns the number to the IF function.
  • IF function consider 0 as FALSE and any other integer other than 0 as TRUE.
  • IF function returns "Is there" as Value if true and "Missing" as value if False.

Example:

Let's get this through with this via using the formula in example.

Here we have a table and we need to extract information from this.

Here we need the confirmation by the ID list. So we use the named range for the ID list. ID named range use for the C2:C14.

So we will use the formula to get the total amount

= IF ( COUNTIF ( ID , G4 ) , "Is there" , "Missing" )

Explanation for the formula:

  1. COUNTIF function keeps the count of ID 900 in the list and returns the count to the IF function.
  2. IF function consider 0 as FALSE and any other integer other than 0 as TRUE
  3. IF function returns "Is there" as Value if true and "Missing" as value if False.

Here the argument arrays to the function is given as cell reference.

As you can see the formula returns the values for the ID number 807 & 953. But returns Missing for the ID number 900. 

Second Method: Use of ISNA & VLOOKUP function.

Syntax of formula:

= IF ( ISNA ( VLOOKUP ( cell_value , list , 1 , 0 )), "Missing" , "Is There" )

Explanation for the formula:

  1. VLOOKUP function looks up for the cell value in the 1st column of the table_array list. The function returns the value if found or else returns #N/A error.
  2. The ISNA function catches the #N/A error and returns TRUE if #N/A error exist or else returns FALSE.
  3. IF function returns "Is there" as Value if FALSE and "Missing" as value if TRUE.

As you see from the above snapshot. The formula returns the "Is There" for the matched ID 807 & 953. But returns "Missing" for the unmatched ID 900. 

Third Method: Use of ISNA & MATCH function.

Syntax of formula:

= IF ( ISNA ( MATCH ( cell_value , list , 0 )), "Missing" , "Is There" )

Explanation for the formula:

  1. MATCH function looks up for the cell value in the table_array list. The function returns the value if found or else returns #N/A error.
  2. The ISNA function catches the #N/A error and returns TRUE if #N/A error exist or else returns FALSE.
  3. IF function returns "Is there" as Value if FALSE and "Missing" as value if TRUE.

As you see from the above snapshot. The formula returns the "Is There" for the matched ID 807 & 953. But returns "Missing" for the unmatched ID 900. 

The above explained 3 examples to find the missing values in the list in excel. All three formulas works fine but there are some points to look for.

Notes: 

  1. VLOOKUP function doesn't look towards the left in the table_array.
  2. The COUNTIF function supports Wildcards ( * , ? ) which helps in extracting values having phrases.
  3. Non - numeric values must be provided in double quotes ("value") or use cell_reference..
  4. See the whole list as the function returns values wherever matches.
  5. The array argument to the function can be given as cell reference or named ranges.
  6. You can customize these formulas as required using other excel function.
  7. The function returns the sum of the values satisfying all the conditions.

Hope you understood how to Find missing values in Excel. Explore more articles on Excel function formulas here. Please feel free to state your query or feedback for the above article. We will assist you.

Related Articles

INDEX-MATCH in Excel

VLOOKUP Multiple Values

VLOOKUP with Dynamic Col Index

Partial match with VLOOKUP function

Vlookup by Date in Excel

17 Things About Excel VLOOKUP

Popular Articles

50 Excel Shortcut to Increase Your Productivity

Edit a dropdown list

Absolute reference in Excel

If with conditional formatting

If with wildcards

Vlookup by date

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube