Excel Function

Question

Find attached a matrix, and picture below

I select Rare (0.5) in a cell and Modera (8) in another cell, i want function to return in a cell from matrix above. Like A (4) with green filled in. My inputs are Rare (0.5) and Moderate (8) and i want output cell to be Green filled in A (0.5) picked from matrix above. Like wise if the input cells are Possible (2) and Critical (50) then the output will be D (100) filled with light orange. I tried Index and Match function but failed.

Auto RM.pdf

I can send the excel if you want.

Please let me know what is the right function i have to use.

Answer 1

Dear @Sethu Balaji Rao,

I hope you’re having a good day.

Hello, thank you for sharing your scenario and the sample.

I understand that you’re trying to dynamically return a value (such as A (4) or D (100)) from your matrix based on two inputs (likelihood and severity) and potentially match the formatting as well.

You mentioned that you already tried using INDEX and MATCH but it didn’t work as expected.

Since this type of issue is very dependent on the exact structure of your worksheet (cell ranges, formatting, hidden spaces, etc.), the most effective way to help you accurately is to review your actual Excel file.

If possible, could you please share your Excel file.

With that, I can:

• Check your current formula and identify exactly why it’s not returning the expected result
• Adjust the INDEX/MATCH logic to fit your exact layout
• Help you implement the correct setup step by step

And, if needed, guide you on how to apply the color formatting (so the result cell reflects the matrix colors like green, yellow, orange, etc.)

In the meantime, just to give you confidence, you are absolutely on the right path. This scenario can be solved with INDEX + MATCH for the value and Conditional Formatting for the color, but the details need to align exactly with your worksheet structure.

Once you share the file, I’ll walk through it with you and provide a precise, working solution tailored to your setup.

Thank you again, and I’m happy to help you get this working end to end!

Please understand that our initial response does not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.  

Looking forward to hearing back from you with any updates or additional details. 

Warm regards, 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".    

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.   

Answer 2

Use INDEX with MATCH for both the row and column of the matrix, then apply conditional formatting to color the result.

Assume this layout (as in the picture):

• RM labels (Rare (0.5), Unlikely (1), …) are in A3:A7
• Consequence labels (Minor (1), Moderate (8), …) are in B2:F2
• The matrix values (A (0.5), A (4), …) are in B3:F7
• Input 1 (e.g. Rare (0.5)) is in A10
• Input 2 (e.g. Moderate (8)) is in A11

In the output cell (say A12) use:

=INDEX($B$3:$F$7,
       MATCH($A10,$A$3:$A$7,0),
       MATCH($A11,$B$2:$F$2,0))

Explanation (based on the documented pattern =INDEX(Table_Array,MATCH(Lookup_Value,Lookup_Array,0),Col_Index_Num)):

• The first MATCH finds the row in A3:A7 that equals the selected RM (e.g. Rare (0.5)).
• The second MATCH finds the column in B2:F2 that equals the selected consequence (e.g. Moderate (8)).
• INDEX returns the intersecting cell from B3:F7 (e.g. A (4)).

To color the result (green, yellow, orange, red), use Conditional Formatting rules on the output cell based on the returned letter/number, for example:

• Format green when the text begins with "A".
• Format light orange when the text begins with "D".

---

References:

• Use Excel built-in functions to find data in a table or a range of cells
• Look up values with VLOOKUP, INDEX, or MATCH