Two good ways to solve this problem in Excel are theCOUNTIF functionand theMATCH function.
Both approaches are explained below.
COUNTIF function
COUNTIFcounts cells in a range that meet a given condition (criteria).
If no cells meet the criteria, COUNTIF returns zero.
The generic syntax for COUNTIF is:
To check for missing invoices, combine COUNTIF with theIF function.
The formula in G5 is:
Notice the COUNTIF formula appears inside the IF function as thelogical_testargument.
Normally, the IF function requires a logical test to return TRUE or FALSE.
When the count is non-zero, The IF function returns “OK”.
Alternative with MATCH
Another approach is to use theMATCH function.
MATCH locates the position of a value in a row, column, or table.
When MATCH finds the lookup value, it returns thepositionof that value in the array as anumber.
If MATCH doesn’t find the lookup value, it returns an #N/A error.
When an invoice number is not found, MATCH returns #N/A.
The ISNUMBER function returns TRUE or FALSE, depending on the result from MATCH.
Choose based on personal preference.
The COUNTIF function requires a range.
This might be important when usingdynamic array formulas.
To read more about this limitation,see this article.
More than one condition can be tested by nesting IF functions.
MATCH supports approximate and exact matching, andwildcards(* ?)
