How to Create an Excel Priority Matrix Formula Based on Two Cell Values
Question details
The user needs to create a formula that returns a priority level by matching the combination of two cell values (such as urgency and importance) against a predefined lookup matrix.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up an automated priority or task management matrix where priority levels are calculated automatically based on specific input criteria.
- Observed behavior
- The formula should correctly output the intersecting priority level from the matrix or a specific status like 'Ongoing' based on custom rule conditions.
Ensure you have set up a clear matrix in your spreadsheet with your first criteria (e.g., urgency values) in the first column and your second criteria (e.g., importance values) across the header row.
Use INDEX and MATCH to Look Up Values in a Matrix
This is the most standard and widely compatible method to find a value at the intersection of a specific row and column in a spreadsheet.
By combining the INDEX and MATCH functions, you can create a dynamic two-way lookup. MATCH finds the relative row and column positions of your inputs, and INDEX retrieves the value at that exact intersection.
Create your matrix in a range, such as G1:J6. Place Urgency labels in the first column (G1:G6) and Importance labels in the top row (G1:J1). Fill the intersecting cells with the corresponding priority levels.
Determine where users will input the two values. For this example, assume Urgency is entered in cell A1 and Importance is entered in cell B1.
Select the cell where you want the priority result to appear. Type the formula: =IFERROR(INDEX($G$1:$J$6, MATCH(A1,$G$1:$G$6,0), MATCH(B1,$G$1:$J$1,0)), "") and press Enter.
Test the formula by changing the values in A1 and B1. The result should automatically update to reflect the corresponding intersecting value in your matrix.
Create a Custom Function with LAMBDA and XMATCH (Microsoft 365)
If you are using a modern version of Excel, you can use the LET, LAMBDA, and XMATCH functions to create a reusable custom formula without needing an on-sheet matrix.
Use the LOOKUP Function for Basic Priority Assignments
A simpler alternative utilizing inline LOOKUP arrays, ideal when you have limited conditions like High, Medium, and Low.
Easily Manage Data Matrices with WPS Spreadsheet
WPS Office provides a powerful, free Spreadsheet tool that fully supports advanced lookup functions like INDEX, MATCH, and LOOKUP. It allows you to build complex priority matrices seamlessly while maintaining perfect compatibility with standard Excel formulas.
- 1. Install the software: Download and install WPS Office for free from the official website.
- 2. Open your workbook: Open your existing Excel workbook or create a new Spreadsheet document.
- 3. Set up your matrix: Input your matrix data and use the standard INDEX and MATCH formulas exactly as you would in Microsoft Excel.
- 4. Save and share: Save your completed matrix securely to your local drive or sync it across devices using WPS Cloud.
Frequently Asked Questions
Why is my INDEX MATCH priority formula returning an #N/A error?
An #N/A error usually means that the lookup value (Urgency or Importance) in your input cells does not exactly match the text in your matrix headers. Check for hidden leading or trailing spaces and ensure the spelling is identical.
How do I add an 'Ongoing' condition before the matrix lookup?
You can wrap your matrix lookup inside an IF statement. For example: =IF(A1="Ongoing", "Ongoing", IFERROR(INDEX(...), "")). This checks for the 'Ongoing' status first, and only runs the matrix calculation if 'Ongoing' is not found.
Can I use wildcards in the MATCH function for my matrix?
Yes, you can use wildcards like asterisks (*) or question marks (?) in your MATCH function by ensuring the third argument (match_type) is set to 0 for an exact match. This allows you to match partial text strings in your matrix headers.
