Lookup & Reference
5x5 Risk Score Buckets & Step-Based Tier Lookup (Sorted INDEX & MATCH)
Compute Likelihood x Impact risk scores (1–25) and map them into editable Low / Moderate / High / Critical governance tiers using sorted step-threshold lookup.
Why sorted step-table lookups beat nested IFs in risk registers
Operational and security risk registers score every risk from 1 to 25 by multiplying Likelihood (1–5) by Impact (1–5). Mapping those scores with 4 nested IFs hardcodes governance thresholds inside every row.
Storing ascending floor thresholds (1 -> Low, 5 -> Moderate, 10 -> High, 16 -> Critical) in E2:F5 and evaluating INDEX($F$2:$F$5, MATCH(D2, $E$2:$E$5, 1)) performs a clean step-function lookup: MATCH(..., 1) finds the largest floor <= Score, so governance committees can adjust tier cutoffs in E2:E5 without touching a single formula. In Arte Sheets, you can also visualize risk counts per tier with a column or bar chart via the toolbar or AI Assistant.
| Risk Item | Likelihood x Impact | Risk Score | Matched Floor | Assigned Risk Tier |
|---|---|---|---|---|
| RSK-01 | 2 x 2 | 4 | 1 | Low (1–4) |
| RSK-02 | 1 x 5 | 5 | 5 (Exact boundary) | Moderate (5–9) |
| RSK-03 | 3 x 4 | 12 | 10 | High (10–15) |
| RSK-04 | 4 x 5 | 20 | 16 | Critical (16–25) |
Step-Based Risk Bucket Lookup via INDEX + MATCH
Requires threshold column $E$2:$E$5 to be sorted in ascending order (1, 5, 10, 16) so match_type = 1 steps cleanly at each floor.
=INDEX($G$2:$G$5, MATCH(B2 * C2, $F$2:$F$5, 1))Count High & Critical Escalation Risks
Counts how many register items meet or exceed the High escalation floor ($F$4 = 10).
=COUNTIF(D2:D5, ">=" & F4)Try the example
Use the original values or change them to test your understanding.