How would I combine these two formulas?

[Poppyrob]

I have two formulas that work. But I would like to combine them into one formula. I have tried and cannot get them nested together correctly.

=INDEX(C6:G10,XMATCH(K5,A6:A10),XMATCH(J5,C4:G4))

=INDEX(C6:G10,XMATCH(M5,A6:A10),XMATCH(L5,C4:G4))

I am using XMatch because if I use Match it will not work. I have 5 ratings, Very Low, Low, Moderate, High, Very High and if I try to match "Low" or "High" the formula will not work. I am guessing because there are other values with the same words in them and it is causing an error. If I select Very Low, Moderate, or Very High match will work.

 

[Joe4]

Without seeing your data and expected results, it can be very difficult to try to provide any assistance.
Can you post a sample of your data and expected results?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Poppyrob]

I am not able to open or use the XL2BB Add in. Policy will not allow me to open it. I do not see a place to attach a workbook, so I am attaching a sample image of what I am doing. The reason I have it broke out like I do is to try and learn how to use the formulas and combine them together. But I just cannot figure out how to combine the two index/match formulas. Thanks for the reply.

 

[Joe4]

I am sorry. I am looking at your example, but I am not understanding what exactly it is you are trying to do.
Can you forget about the formulas for a minute, and walk me through an example of what you are trying to do with your data, step-by-step, in plain English?
Show me exactly what you want to see, and explain in the detail the path on how you are getting there.

Thanks

 

[Poppyrob]

This is a risk scoring formula based on the matrix. A value is chosen in J2, K2, L,2, M2. Then the overall risk score is calculated in N2.
The first formula matches what is in J2 to C4-G4
The second formula matches what is in K2 to A6-A10
Then we index that to get a value (Value 1 lets say) This gives us the overall likelihood.

The third formula matches what is in L2 to C4-G4
The fourth formula matches what is in M2 to A6-A10
The we index that to get a value (Value 2 lets say) This gives us the overall impact

The we would index Value 1 and Value 2 to get the Overall Risk Rating.

What I am trying to do it combine the index formulas so I dont have to manually index the ratings. As the values in J2-M2 are updated the overall score is as well.

 

[Cubist]

Combine in what way? Suppose you get 2 and 3 for the 2 formulas under "combined matches". How do you want to combine 2 and 3?

 

[Poppyrob]

Maybe this will make it easier to explain. If we assign scoring text to each one. Those would each return a scoring text value. Lets say 2 = Low, and 3 = Moderate. I want the combined formula to then look at "Low" in C4-G4 and "Moderate" in A6-A10 and return the indexed value.

 

[Joe4]

I am sorry, I just cannot wrap my head around the logic being used.
I think I am going to have to bow out of this one.
Hopefully, Cubist understands it better than I do.

 

[Poppyrob]

So I was able to get this to almost do what I want. I have 3 of the 4 input values working. It indexes H2 and I2 and come us with a value. In the example that value is "Low". The current working formulas takes the "Low" and indexes it with column K. This returns a moderate.
What I need to do is add Column J to the equation. I need to index column J and K to return a value, then that value would be indexed against the "Low" value to give me the overall score.

Current working formula =INDEX($B$3:$F$7,MATCH(INDEX($B$3:$F$7,MATCH(H2,$A$3:$A$7,0),MATCH(I2,$B$2:$F$2,0)),$A$3:$A$7,0),MATCH(K2,$B$2:$F$2,0)).

I know its hard to explain without being able to upload a spreadsheet.

 

[Cubist]

I'm a bit more confused now. Initially, you have a table with likelihood vs. level of impact, then you swap the numerical value with categories "low", "very low", ect... Now your table is for an Overall Likelihood. What are the rows and columns mean and how does that come into play?
You have 5 values you're looking up but a table is only 2 dimensions (rows and columns) how does that relate to the table?
Can you walk through a logic for one specific example?