Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Find missing values

If you want to search for the presence of a certain entry in a list then making a comparison of those entries with that of the list containing the data will be helpful. The formula presented in this article will make use of IF and COUNTIF statements.

Find Missing Values

Missing values from a list can be checked by using the COUNTIF function passed as a logical test to the IF function. After the logical test, if the entry is found then a string “OK” is returned otherwise “Missing” is returned.

Formula

=IF (COUNTIF(list,value),“OK”,“Missing”)

Explanation

To find the missing entries from a list, a conditional COUNT check is made which counts only if the condition passed to it becomes true. If this count check is true then the IF condition covering it intimates about the presence of that certain entry in the list.

How to Find the Missing Values

To find the missing values from a list, define the value to check for and the list to be checked inside a COUNTIF statement. If the value is found in the list then the COUNTIF statement returns the numerical value which represents the number of times the value occurs in that list.

The COUNTIF statement returns the results which play a role as the first argument of IF statement for the logical test to be performed. If the count returned by COUNTIF statement is zero then the IF statement returns that value which is passed when a logical test fails. In the other case, if COUNTIF statement returns some number IF statement is operated with a logical test to be true.

Example 1

An example sheet has been considered which has an array named as “list” containing serial numbers (Sr. No.). A separate search list has been made, which enlists the entries that are needed to be checked in the list. The sample sheet is shown below:

Figure1. Sample sheet for finding the missing value

To find the missing value in the cell E3, enter the following formula in F3 to check its status.

=IF(COUNTIF(list,E3),"OK","MISSING")

Figure2. Using the formula in F3 to look for the missing value (in E3) in the list (B3:B8)

The results of this formula can be observed in the snapshot below:

Figure3. Updated status of missing and available values

It can be seen that the entries 1256 and 1260 are present in the array “list” as its 2nd and 4th entries respectively. Therefore, their status is updated as OK. While the entries 1258 and 1259 are not available and are updated as “MISSING”.

Alternative Formulae to Find Missing Values

Example 2

Missing values can also be found with the help of MATCH function. MATCH will look for the position of a certain item and will generate a #N/A error if the value is not found. This check can be passed as the logical test to the IF statement which will update the status of the entry accordingly. The generic formula for finding the missing values using the MATCH function is written below:

=IF(ISNA(MATCH(value,range,0)),"MISSING","OK")

The results obtained by this function are the same as shown below:

Figure4. Using the MATCH function with ISNA and IF function to find missing values

Example 3

Missing values can also be found with the help of VLOOKUP function. VLOOKUP returns a #N/A error if a value is not found from the list. In place of MATCH function, VLOOKUP function is used here with ISNA function to find the missing values.

The following figure shows the results with VLOOKUP function with the formula mentioned in it:

Figure5. Using the VLOOKUP function with ISNA and IF function to find missing values

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
I have a few columns of text in columns "A" and "B" I want to compare those columns to columns "L" and "M" in order to determine if these recurring names come up. I am trying to determine if a donor has donate from year to year and can't figure this out.
Solved by O. L. in 21 mins
Is it possible to have a function (like a COUNTIF) that will count sells that meet specific criteria, but the range is limited to the 10 cells in the range that immediately precede a blank cell? So for example, the general range is A1:Z1, but I want the range to be specific to the last ten cells immediately prior to the first blank cell in the general range. Thank you.
Solved by O. F. in 26 mins
I used the formula =COUNTIF for cells to define adding the number of cells that have numbers totaling 0 or above. However, When I try using it in another set of columns it is not doing what I need it to. Now, the column that is not working has both colored cells and formula'd numbers within the cells. I dont understand what I am doing wrong, please help!
Solved by C. H. in 20 mins
Need help putting together formula where it counts the number of sales but only if the average annual premium is above $360. So a sales = Sum(N:2:N:48)/360....but not greater than COUNTIF(N:2:N:48) ""
Solved by O. J. in 43 mins
I need to be able to count certain letters as numbers and then add them all up =((COUNTIF(P19:P28,"A")*12),(COUNTIF(P19:P28,"B")*12),(COUNTIF(P19:P28,"C")*8),(COUNTIF(P19:P28,"D")*8),(COUNTIF(P19:P28,"E")*12),(COUNTIF(P19:P28,"F")*8),(COUNTIF(P19:P28,"G")*12),(COUNTIF(P19:P28,"H")*8),(COUNTIF(P19:P28,"I")*12)) this is what I was trying to use
Solved by V. F. in 31 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc