When you are dealing with loads of data and you need to check for whether a particular value/ number/ product/ order is in the category or not, this is what you can do.

To deal with such a situation, or in simple words to know for whether a value is missing or not in the range, you can use IF Function and COUNTIF Function to find the same.

Here’s an example for how to find the same:

find missing values

As you can see in the image above, we have a bunch product IDs with colour and sales of the same.

Now, let’s say, we need to check over some product ID for whether that particular product is available in the stock or went missing (in other words- not arrived, or delivered or etc. whatever you feel like saying)

find missing values

Step 1: Input the formula =IF(

We will begin to find the issuing values by inputting the IF Function. As you can see in the image above, we are looking for product ID 459, for whether the same is available or missing in the stock.

The IF Function is as follows:
=IF(

Step 2: Input the required values in the formula

The following formula requires logical test, value if true, value if false.

find missing values

Step 3: Input the formula =COUNTIF(

In the logical test, we will input the COUNTIF Function which is as follows:
=COUNTIF(

Step 4: Input the required values in the formula

The following formula requires range, and criteria.

find missing values

The range will be the product range; our lookup value is the product itself.

So, as per the example above, the range will be B2:B12.

find missing values

After inputting the range, we will come to the point of criteria. Now as our lookup value is 459, so the criteria will be the same i.e. cell F4.

find missing values

Then we will close the COUNTIF Function and move forward with the IF Function.

As we have completed the work of logical test, so we will come to the point of value if true. If the product ID 459 i.e. cell F4 is available in the product range then what would be the value that you would want to reflect on the screen?

In the above example, we have written “Available” itself.

find missing values

Then we will move to the value id false this means that if the product ID 459 (cell F4) is not available in the product range, what would be the value that you would want to reflect on the screen for the same?

In the above example, we have used the word “Missing” for the same,

*Please note: whether it’s the value if true or it’s the value if false, they both need to be jotted down in the quotation marks only, as we are applying some specifications in the formula, so its necessary to write the same in the quotation marks or else the function won’t work and will end up with an error.

find missing values

Step 5: Cross- check the formula and proceed

So, till now, the overall formula becomes as follows:

=IF(COUNTIF(B2:B12,F4),”Available”,”Missing”)

find missing values

Now all you have to do is press enter and BAMMM!!! The answer is right on the screen. The product ID 459 is available in the product range.

Now, simply drag down the formula for the rest of the product IDs to find out for whether a product is missing in the stock or not!

find missing values

It’s easy, isn’t it?