Explanation
The goal of this formula is to verify whether the values in column B follow the format xx-xxxx-xxx, where “x” represents any single character. The IF function doesn’t support wildcards directly, so we can’t use IF by itself. Instead, we can combine the IF function with the COUNTIF function, which does support wildcards.
Excel wildcards
Excel supports three wildcards that can be used in formulas:
- Asterisk (*) - zero or more characters
- Question mark (?) - any one character
- Tilde (~) - escape for literal character (~*) a literal question mark (~?), or a literal tilde (~~).
IF + COUNTIF
Unlike several other frequently used functions, the IF function does not support wildcards. However, you can use the COUNTIF or COUNTIFS functions inside the logical test of IF for basic wildcard functionality . In the example shown, the formula in D5 is:
=IF(COUNTIF(B5,"??-????-???"),"ok","invalid")
Working from the inside out, the logical test inside the IF function is based on the COUNTIF function :
COUNTIF(B5,"??-????-???") // returns 1 or 0
In this context, COUNTIF counts cells matching the pattern “??-????-???”, with the question mark (?) representing any single character. Because the range provided to COUNTIF is just one cell, the result will always be 1 or zero. Inside the IF function, Excel will evaluate any non-zero number as TRUE and zero as FALSE. When COUNTIF returns the number 1 (indicating that the value in B5 consists of 11 characters with two hyphens as described by the pattern), IIF interprets this as TRUE and returns “ok”. When COUNTIF returns zero, IF will return “invalid”. The values returned by IF can be customized as needed.
IF + SEARCH
Another way to use wildcards with the IF function is to combine the SEARCH and ISNUMBER functions to create a logical test. This works because the SEARCH function supports wildcards:
=IF(ISNUMBER(SEARCH("??-????-???",B5)),"ok","invalid")
The SEARCH function returns the position of a match inside a text string. If SEARCH does not find a match, it returns a #VALUE! error. The ISNUMBER function is used to convert the result from SEARCH into TRUE or FALSE. Like COUNTIF, SEARCH supports wildcards, so we can use the same pattern to check for invalid codes. For more details on how SEARCH and ISNUMBER can be used together, see this page.
Note: The COUNTIF function has a limitation – the range argument must be a range . It’s not possible to pass an array from another function into COUNTIF. If you run into this problem, you can use SEARCH + ISNUMBER option above instead.
Explanation
The goal is to calculate the correct invoice status (“OK”, “Paid”, or “Overdue”) using the following rules:
- If there is an “x” in the “Paid” column, return “Paid”.
- If there is not an “x” in the “Paid” column, and if the age is less than 31 days, return “OK”
- If there is not an “x” in the “Paid” column, and if the age is not less than 31 days, return “Overdue”
This problem can be solved by nesting one IF function inside another.
Calculating age
To calculate invoice age in days, the formula in cell E5, copied down, uses the TODAY function :
=TODAY()-C5
This works because Excel dates are just serial numbers. At the time of this writing, the date is May 20, 2023. In Excel’s date system, this is the number 45066. The due date of March 30, 2023, is the number 45015. Excel evaluates the formula above like this:
=TODAY()-C5
=45066-45015
=51
IF function
The IF function in Excel runs a test, then performs one action if the result is TRUE, and a different action if the result is FALSE. The generic syntax for IF looks like this:
=IF(test,true_result,false_result)
The first argument is the logical test, and the second argument is the result (or calculation) to return when the test is TRUE. The third argument is the “else” — the value or calculation to return if the result of the logical test is FALSE.
IF paid
In the example shown, the first thing to check is if the invoice has been paid or not. We do this by checking the Paid column for an “x” as seen below. Notice that both “x” and “Paid” are enclosed in double quotes because they are text values:
=IF(F5="x","Paid")
Translation: If F5 equals “x”, return “Paid”.
At this point, we have not provided anything for the value if false. This means the IF function will return FALSE when F5 is empty. If this was the only thing we were checking, we might want to provide an empty string ("") like this:
=IF(F5="x","Paid","")
In Excel, an empty string will not display anything. It will look like an empty cell.
IF overdue
The remaining task is to check the age of the invoice. To extend the formula to check for an overdue status, we use another IF formula like this:
IF(E5<31,"OK","Overdue")
Translation: If E5 is less than 31, return “OK”, else return “Overdue”.
Notice the value 31 is a number and, therefore, not enclosed in quotes. This formula will return “OK” for any age below 31 and “Overdue” for any age over 30. This number can be customized as desired.
The final step in the problem is to combine the two IF formulas above. We do this by starting off with the first IF:
=IF(F5="x","Paid")
Then we extend the formula by nesting the second IF inside the first IF:
=IF(F5="x","Paid",IF(E5<31,"OK","Overdue"))
Translation: If F5 equals “x”, return “Paid”. Else if E5 is less than 31, return “OK”, else return “Overdue”.
Notes
- The technique of placing one IF inside another is called " nesting ." You will sometimes hear a formula like this called a “Nested IF formula”. This page has many examples .
- Remember to enclose text values inside IF in double quotes (""), but do not quote numbers or operators. See How to use the IF function for more details.
