Excel - Risk Rating calculation using "If(or)" function

Occasional Contributor

I have copied this formula - =@IF(OR($M10="",$L10=""),"",INDEX(Matrix_Rating,MATCH($M10,Matrix_Likelihood,0),VALUE(LEFT($L10))))

from one Excel spreadsheet to another to perform the same Risk Rating task, however it does not work on the second Excel spreadsheet. I have checked to see if the original sheet has a  reference page but it doesn't. So I am confused as to why this formula works on one Excel spreadsheet and not the other. Can you help me please.

16 Replies



Here's your problem:


The one that says #REF

I don't see Matrix_Rating defined there too



Yes your right. It must refer to that "Risk_Matrix" in another document.

So I guess I'll have to do it using an inbuilt table in this document.


Thanks for your help.



You can define that range in the Risk_Matrix Sheet as a named range "Matrix_Rating and your formula should work as is.

I'm still learning how to use advanced features in Excel, If I send this file to you, could you fix this for me please.



What's wrong with it, I will try to help you.

I don't know how to define the Table range in the formula for the Risk Rating cell to auto fill with the Category of Risk (H18 etc.) and the colour for the risk rating.



I downloaded the file you shared, I will study it and I will ask you questions to verify if my comprehension to the item is correct before applying the appropriate formula

Okay Thanks.
I appreciate your help.



Here you go.  The formula is:

=INDEX(Consequence,MATCH(LEFT('Electrical-Commission '!W15,1),LEFT('Risk Matrix'!$A$5:$A$10,1),0),MATCH(LEFT(X15,1),LEFT('Risk Matrix'!$A$5:$F$5,1),0))


and it seems to bring the color along with the color in the matrix as well.




Hello Yea,


Thanks for that doing this for me.

When I put the formula into the first Risk rating cell "Y15" and press enter the following error message comes up. Can you send back the document that you have corrected please. 


I did it was attached to my last response, look at the bottom of my previous response.

best response confirmed by Jblake998 (Occasional Contributor)


This is the workbook that i sent on my response, and it works. Isn't it the right value?



hello Yea,

Sorry I didn't see the attachment. it is very small.
I am happy to pay you for your time.
How much do I owe you please.



1,000,000.00 lol, just mark the post as the best answer.  Thank you



Thank you so much.
I really appreciate your help.



You are very welcome John.