How to Create an Excel IF and AND Risk Assessment Formula
Question details
The user needs a formula to evaluate risk levels using five consequence labels and five likelihood labels selected from drop-down lists.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Building a 5x5 risk assessment matrix to output one of five final risk results based on variable inputs.
- Observed behavior
- Requires mapping 25 possible consequence-and-likelihood combinations to their correct risk categories automatically.
Ensure your consequence and likelihood drop-down lists are properly created using Data Validation, and that the text perfectly matches the values you will use in your formula.
Use an INDEX MATCH Lookup Table
Instead of writing a massive 25-condition nested IF statement, building a lookup matrix is much easier to maintain and drastically reduces formula complexity.
A lookup table separates your raw data from your calculation formulas. By creating a physical 5x5 grid in your worksheet, you can easily change risk tolerances in the future without ever editing the formula itself.
The INDEX and MATCH functions work together to find the exact intersection of your chosen Consequence and Likelihood.
In a new worksheet or an empty area, list your five Consequence labels down a column (e.g., A2:A6) and your five Likelihood labels across a row (e.g., B1:F1).
Fill the 25 intersecting cells (B2:F6) with the correct final risk result corresponding to each combination (e.g., Low, Medium, High, Extreme).
Select the cell where you want the dynamic risk result to display. Type the formula: =INDEX(B2:F6, MATCH(Consequence_Input_Cell, A2:A6, 0), MATCH(Likelihood_Input_Cell, B1:F1, 0)).
Press Enter. The cell will now automatically retrieve the correct risk level whenever the input drop-downs change.
Build a Nested IF and AND Formula
Create a logical test that evaluates each consequence and likelihood combination directly within a single formula.
Easily Manage Risk Assessment Matrices in WPS Spreadsheet
WPS Office Spreadsheet offers full support for advanced logical functions and array formulas, allowing you to perfectly execute complex nested IFs and multi-criteria lookup grids without breaking a sweat.
- 1. Open Your Data File: Launch WPS Spreadsheet and open the document containing your risk management framework.
- 2. Access the Formula Tools: Select the cell for your risk output, click the 'Formulas' tab on the top ribbon, and click the 'Insert Function' icon.
- 3. Insert Functions: Search for the 'INDEX' or 'IF' function, select it, and follow the clear on-screen dialog prompts to connect your consequence and likelihood cells.
- 4. Save the Result: Click 'Save' or press Ctrl+S to keep your formulas intact. You can easily share your fully functional file in standard .xlsx format.
Frequently Asked Questions
Why is my nested IF formula returning an error?
A nested IF error is usually caused by missing parentheses at the end of the formula, missing quotation marks around text labels, or accidentally exceeding the nested IF limit. A function building tool can help highlight missing syntax.
Can I use the IFS function instead of nested IFs for this?
Yes. If you are using a newer version of Excel or WPS Office, you can replace nested IF functions with the IFS function to evaluate multiple logical tests without having to type 'IF' repeatedly.
How do I create drop-down lists for consequence and likelihood?
Go to the Data tab, select Data Validation, and choose 'List' under the Allow settings. You can then input your five labels separated by commas or highlight an existing cell range containing those labels.
