How to Find, Count & Fill Missing Values in Excel

• Press Enter.
• Drag down the Fill handle icon.

Formula Breakdown

• COUNTIF($B$5:$B$12,E5)

This function looks for the value in cell E5 within the range B5:B12. If the value in cell E5 exists within the range B5:B12, then it returns 1. If the value doesn’t exist within the range, then it returns 0.

• IF(COUNTIF($B$5:$B$12,E5),”Available”,”Not Available”)

The IF function returns Available if the COUNTIF function returns 1. Otherwise, it returns Not Available.

• Press Enter.
• Drag down using the Fill Handle icon

Formula Breakdown

• MATCH(E5,$B$5:$B$12,0)

This function finds the position of the value in cell E5 within the range B5:B12. If the value exists within the range, then the function returns that value’s position. When the value doesn’t exist within the range, the function returns #N/A.

• ISNA(MATCH(E5,$B$5:$B$12,0))

If the MATCH function returns the position then the ISNA function returns FALSE. When the MATCH function returns #N/A then the ISNA function returns TRUE.

• IF(ISNA(MATCH(E5,$B$5:$B$12,0)),”Not Available”,”Available”)

If the ISNA function returns FALSE, then the IF function returns Available. Other than that, it returns Not Available.

• Press Enter.
• Uuse the Fill handle to drag down.

Formula Breakdown

• VLOOKUP(E5,$B$5:$B$12,1,FALSE)

The VLOOKUP function looks for the corresponding value of cell E5 within the range B5:B12. If the corresponding value exists, then the function returns that value. Otherwise, the function returns #N/A.

• ISNA(VLOOKUP(E5,$B$5:$B$12,1,FALSE))

If the VLOOKUP function returns any corresponding value, then the ISNA function returns FALSE. When the VLOOKUP function returns #N/A, then the ISNA function returns TRUE.

• IF(ISNA(VLOOKUP(E5,$B$5:$B$12,1,FALSE)),”Not Available”,”Available”)

If the ISNA function returns FALSE, then the IF function returns as Available. If the ISNA function returns TRUE, then the IF function returns Not Available.

• Press Enter.
• Drag down using the Fill handle icon.

Formula Breakdown

• MATCH(E5,$B$5:$B$12,0)

This function finds the position of the value in cell E5 within the range B5:B12. If the value exists within the range, then the function returns that value’s position. When it doesn’t exist within the range, the function returns #N/A.

• ISNUMBER(MATCH(E5,$B$5:$B$12,0))

If the MATCH function returns the position, then the ISNUMBER function returns TRUE. Otherwise, it returns FALSE if the MATCH function returns #N/A.

• IF(ISNUMBER(MATCH(E5,$B$5:$B$12,0)),”Available”,”Not Available”)

Finally, for the TRUE statement returned by the ISNUMBER function, the IF function returns Available. And for the FALSE statement, it returns Not Available.

Formula Breakdown

• COUNTIF(E5:E8,B5:B12)

First, the COUNTIF function goes through the range B5:B12 and returns 1 if any value in range B5:B12 exists in range E5:E8. If not, then it returns 0.

• FILTER(B5:B12,COUNTIF(E5:E8,B5:B12)=0)

The FILTER function returns those values from range B5:B12, for which the COUNTIF function returns 0.

• Press Enter.

Formula Breakdown

• COUNTIF(D5:D7,B5:B12)

First, the COUNTIF function goes through the range B5:B12 and returns 1 if any value is found in the range B5:B12 and range E5:E8. If not, then it returns 0.

• SUMPRODUCT(–(COUNTIF(D5:D7,B5:B12)=0))

The SUMPRODUCT function will return the sum of the 0 returned by the COUNTIF function.