Formula Errors

How to Use an Excel Formula to Classify Amber and Red Values in a Row

Rana Garcia Oct 7, 2026 869 views

Question details

The user needs a formula to scan a row for specific text values ('amber' and 'red') and return a distinct numeric code based on their presence.

How to Classify Amber and Red Values in an Excel Row Using Formulas

Product: Excel
Device & OS: not provided
Scenario: Analyzing project statuses, risk logs, or tracking spreadsheets where rows contain text indicators like 'amber' or 'red' across multiple columns.
Observed behavior: The user wants to generate a unified numerical output: 0 for neither, 1 for amber only, 2 for red only, and 3 for both.

Before you start

Ensure your target columns contain the exact text 'amber' and 'red' without extra trailing spaces, as the standard MATCH function relies on exact text matching to work correctly.

Solution 1Recommended

Use the ISNUMBER and MATCH Formula Combination

This method combines ISNUMBER and MATCH functions to assign a mathematical weight to the presence of 'amber' and 'red', calculating a unique numerical code for every possible outcome.

The MATCH function searches for a specific string within a range. By wrapping it in the ISNUMBER function, it evaluates to TRUE (treated as 1 in math operations) if the text is found, and FALSE (0) if not.

By multiplying the result of the 'red' check by 2 and adding it to the 'amber' check, the formula generates four distinct combinations: 0, 1, 2, and 3.

Select the target cell

Click on the cell where you want the classification code to appear for the first row of your data (for example, cell X2).

Enter the classification formula

Type the formula: =ISNUMBER(MATCH("amber",$B2:$W2,0))+2*ISNUMBER(MATCH("red",$B2:$W2,0)) and adjust the range $B2:$W2 to match your actual data columns.

Apply the formula to the remaining rows

Press Enter to apply the formula. Then, click the small square at the bottom-right corner of the cell (fill handle) and drag it down to apply the classification to the rest of your dataset.

Use the ISNUMBER and MATCH Formula Combination

Expected Formula Outputs: The result will be 0 if neither status is present, 1 if only 'amber' is found, 2 if only 'red' is found, and 3 if both statuses are present in the row.

Efficient Data Analysis with WPS Office

Easily Classify Data and Execute Complex Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical and lookup functions like ISNUMBER and MATCH, allowing you to seamlessly process complex conditional data and automate status classifications.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the file containing the data you want to classify.
2. Select the status column: Click the first cell in your target column to begin entering your classification formula.
3. Input the formula and fill down: Paste the ISNUMBER and MATCH formula, press Enter, and use the drag-to-fill feature to quickly classify all rows in your project.

Fully compatible with Microsoft Excel formulas, functions, and file formats (XLSX).User-friendly interface for faster data analysis and status tracking.Completely free to use, highly efficient, and lightweight on system resources.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the formula returning 0 even though 'amber' or 'red' is present in the row?

This usually happens due to hidden leading or trailing spaces in the cells containing 'amber' or 'red'. You can clean your data using the TRIM function, or use wildcard characters in the MATCH function to ignore extra text, like this: MATCH("*amber*",$B2:$W2,0).

Can I change the numeric codes to text labels like 'High Risk' or 'Moderate'?

Yes, you can wrap the entire formula in the CHOOSE function to convert the numbers into text. For example: =CHOOSE((your_formula)+1, "No Status", "Moderate", "High Risk", "Critical"). The +1 is required because CHOOSE indexing starts at 1, not 0.

Is the MATCH formula used for this classification case-sensitive?

No, the MATCH function used in this manner is not case-sensitive. It will treat 'Red', 'RED', and 'red' as identical matches. If you need case-sensitive matching, you would need to use an array formula combining EXACT and OR functions instead.

Excel & Data Problems