How to Use Excel IFS Formula to Check for Specific Text
Question details
The user needs an Excel formula to conditionally calculate VAT based on whether a cell contains a specific text string (like "RO") or just numeric values.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating conditional VAT percentages depending on the presence of a specific alphanumeric prefix in a data cell.
- Observed behavior
- Needs to apply a 19% rate if the text "RO" is present, and a 0% rate if the cell contains only numbers or lacks the specific text.
Identify the target cell containing your alphanumeric code and determine the base value cell for your calculation. Note that the SEARCH function is not case-sensitive.
Use IFS combined with ISNUMBER and SEARCH
Combine IFS, ISNUMBER, and SEARCH functions to safely check for a text substring and apply the corresponding calculation without returning an error.
The SEARCH function looks for specific text (like "RO") within a cell. If found, it returns the numerical starting position. If not found, it returns a #VALUE! error.
By wrapping the SEARCH function in ISNUMBER, we convert the result into a clean TRUE or FALSE logic, which the IFS function can then use to determine the correct tax rate.
Click on the cell where you want the final VAT calculation to appear.
Enter the formula: =IFS(ISNUMBER(SEARCH("RO",$L$3)),$Q$12*19%,NOT(ISNUMBER(SEARCH("RO",$L$3))),$Q$12*0). Replace $L$3 with your text cell and $Q$12 with your base amount cell.
Press Enter to apply the formula. If you need to calculate for a whole column, drag the fill handle down to apply the formula to other cells.
Calculate Conditional Formulas Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical and text functions like IFS, ISNUMBER, and SEARCH, allowing you to seamlessly process complex alphanumeric data and calculate taxes without compatibility issues.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data.
- 2. Select the result cell: Click on the cell where the tax result should be displayed.
- 3. Use formula autocomplete: Type the equals sign (=) and begin typing IFS to bring up the formula autocomplete suggestions.
- 4. Enter the nested functions: Input the combination of ISNUMBER and SEARCH functions alongside your conditions, then press Enter to calculate.
Frequently Asked Questions
Why does my SEARCH function return a #VALUE! error?
The SEARCH function returns a #VALUE! error if it cannot find the specified text within the cell. Wrapping it in the ISNUMBER function handles this error gracefully by returning FALSE instead, preventing the error from breaking your entire formula.
Can I use an IF statement instead of IFS?
Yes, for simple binary conditions (e.g., applying 19% or 0%), a standard IF formula is highly efficient: =IF(ISNUMBER(SEARCH("RO",L3)), Q12*19%, 0). The IFS function is generally more useful when you have three or more conditions to test simultaneously.
How do I check for multiple different text strings?
You can add more conditions within the IFS function. For example, to check for "RO" or "UK": =IFS(ISNUMBER(SEARCH("RO",L3)), rate1, ISNUMBER(SEARCH("UK",L3)), rate2, TRUE, default_rate).
