I have a problem with using the MATCH formula to return the correct 'ranking' from my named array.
I have the following:
Cell Q3 - with options VL,L,M,H,VH
Cell R3 - with options VL,L,MLH,VH
Cell T3 should return an overall rating depending on what the user has selected from Cell Q3 and R3.
I have a named range (Matrix1) which shows the what values should be returned (this may or may not have copied correctly into this post.
I tried the following formula as somewhat of a stab in the dark but returned with a #N/A error:
=OFFSET(Matrix1,MATCH(Q3,Matrix1,0),MATCH(R3,Matrix1,0))
Can anyone help with what I should be using to do what I need this to do here?
[TABLE="width: 448"]
<colgroup><col width="64" style="width: 48pt;" span="7"></colgroup>
<tbody>[TR]
[TD="class: xl69, width: 64, bgcolor: transparent, align: center"]Impact[/TD]
[TD="class: xl80, width: 384, bgcolor: transparent, colspan: 6"]Probability[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"]A: VH[/TD]
[TD="class: xl71, bgcolor: transparent"]B: H[/TD]
[TD="class: xl71, bgcolor: transparent"]C: M[/TD]
[TD="class: xl71, bgcolor: transparent"]D: L[/TD]
[TD="class: xl71, bgcolor: transparent"]E: VL[/TD]
[TD="class: xl72, bgcolor: transparent"]F: None[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]1: VH[/TD]
[TD="class: xl74, bgcolor: black"]1-VH[/TD]
[TD="class: xl75, bgcolor: red"]2-H[/TD]
[TD="class: xl75, bgcolor: red"]2-H[/TD]
[TD="class: xl75, bgcolor: red"]2-H[/TD]
[TD="class: xl76, bgcolor: #FFFF99"]3-M[/TD]
[TD="class: xl77, bgcolor: transparent"]None[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]2: H[/TD]
[TD="class: xl75, bgcolor: red"]2-H[/TD]
[TD="class: xl75, bgcolor: red"]2-H[/TD]
[TD="class: xl76, bgcolor: #FFFF99"]3-M[/TD]
[TD="class: xl76, bgcolor: #FFFF99"]3-M[/TD]
[TD="class: xl78, bgcolor: #99CCFF"]4-L[/TD]
[TD="class: xl77, bgcolor: transparent"]None[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]3: M[/TD]
[TD="class: xl75, bgcolor: red"]2-H[/TD]
[TD="class: xl76, bgcolor: #FFFF99"]3-M[/TD]
[TD="class: xl76, bgcolor: #FFFF99"]3-M[/TD]
[TD="class: xl78, bgcolor: #99CCFF"]4-L[/TD]
[TD="class: xl78, bgcolor: #99CCFF"]4-L[/TD]
[TD="class: xl77, bgcolor: transparent"]None[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]4: L[/TD]
[TD="class: xl78, bgcolor: #99CCFF"]4-L[/TD]
[TD="class: xl78, bgcolor: #99CCFF"]4-L[/TD]
[TD="class: xl78, bgcolor: #99CCFF"]4-L[/TD]
[TD="class: xl78, bgcolor: #99CCFF"]4-L[/TD]
[TD="class: xl79, bgcolor: #D6DCE4"]5-VL[/TD]
[TD="class: xl77, bgcolor: transparent"]None[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: transparent"]5: VL[/TD]
[TD="class: xl78, bgcolor: #99CCFF"]4-L[/TD]
[TD="class: xl78, bgcolor: #99CCFF"]4-L[/TD]
[TD="class: xl79, bgcolor: #D6DCE4"]5-VL[/TD]
[TD="class: xl79, bgcolor: #D6DCE4"]5-VL[/TD]
[TD="class: xl79, bgcolor: #D6DCE4"]5-VL[/TD]
[TD="class: xl77, bgcolor: transparent"]None[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: transparent"]6: None[/TD]
[TD="class: xl77, bgcolor: transparent"]None[/TD]
[TD="class: xl77, bgcolor: transparent"]None[/TD]
[TD="class: xl77, bgcolor: transparent"]None[/TD]
[TD="class: xl77, bgcolor: transparent"]None[/TD]
[TD="class: xl77, bgcolor: transparent"]None[/TD]
[TD="class: xl77, bgcolor: transparent"]None[/TD]
[/TR]
</tbody>[/TABLE]
I have the following:
Cell Q3 - with options VL,L,M,H,VH
Cell R3 - with options VL,L,MLH,VH
Cell T3 should return an overall rating depending on what the user has selected from Cell Q3 and R3.
I have a named range (Matrix1) which shows the what values should be returned (this may or may not have copied correctly into this post.
I tried the following formula as somewhat of a stab in the dark but returned with a #N/A error:
=OFFSET(Matrix1,MATCH(Q3,Matrix1,0),MATCH(R3,Matrix1,0))
Can anyone help with what I should be using to do what I need this to do here?
[TABLE="width: 448"]
<colgroup><col width="64" style="width: 48pt;" span="7"></colgroup>
<tbody>[TR]
[TD="class: xl69, width: 64, bgcolor: transparent, align: center"]Impact[/TD]
[TD="class: xl80, width: 384, bgcolor: transparent, colspan: 6"]Probability[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"]A: VH[/TD]
[TD="class: xl71, bgcolor: transparent"]B: H[/TD]
[TD="class: xl71, bgcolor: transparent"]C: M[/TD]
[TD="class: xl71, bgcolor: transparent"]D: L[/TD]
[TD="class: xl71, bgcolor: transparent"]E: VL[/TD]
[TD="class: xl72, bgcolor: transparent"]F: None[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]1: VH[/TD]
[TD="class: xl74, bgcolor: black"]1-VH[/TD]
[TD="class: xl75, bgcolor: red"]2-H[/TD]
[TD="class: xl75, bgcolor: red"]2-H[/TD]
[TD="class: xl75, bgcolor: red"]2-H[/TD]
[TD="class: xl76, bgcolor: #FFFF99"]3-M[/TD]
[TD="class: xl77, bgcolor: transparent"]None[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]2: H[/TD]
[TD="class: xl75, bgcolor: red"]2-H[/TD]
[TD="class: xl75, bgcolor: red"]2-H[/TD]
[TD="class: xl76, bgcolor: #FFFF99"]3-M[/TD]
[TD="class: xl76, bgcolor: #FFFF99"]3-M[/TD]
[TD="class: xl78, bgcolor: #99CCFF"]4-L[/TD]
[TD="class: xl77, bgcolor: transparent"]None[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]3: M[/TD]
[TD="class: xl75, bgcolor: red"]2-H[/TD]
[TD="class: xl76, bgcolor: #FFFF99"]3-M[/TD]
[TD="class: xl76, bgcolor: #FFFF99"]3-M[/TD]
[TD="class: xl78, bgcolor: #99CCFF"]4-L[/TD]
[TD="class: xl78, bgcolor: #99CCFF"]4-L[/TD]
[TD="class: xl77, bgcolor: transparent"]None[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]4: L[/TD]
[TD="class: xl78, bgcolor: #99CCFF"]4-L[/TD]
[TD="class: xl78, bgcolor: #99CCFF"]4-L[/TD]
[TD="class: xl78, bgcolor: #99CCFF"]4-L[/TD]
[TD="class: xl78, bgcolor: #99CCFF"]4-L[/TD]
[TD="class: xl79, bgcolor: #D6DCE4"]5-VL[/TD]
[TD="class: xl77, bgcolor: transparent"]None[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: transparent"]5: VL[/TD]
[TD="class: xl78, bgcolor: #99CCFF"]4-L[/TD]
[TD="class: xl78, bgcolor: #99CCFF"]4-L[/TD]
[TD="class: xl79, bgcolor: #D6DCE4"]5-VL[/TD]
[TD="class: xl79, bgcolor: #D6DCE4"]5-VL[/TD]
[TD="class: xl79, bgcolor: #D6DCE4"]5-VL[/TD]
[TD="class: xl77, bgcolor: transparent"]None[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: transparent"]6: None[/TD]
[TD="class: xl77, bgcolor: transparent"]None[/TD]
[TD="class: xl77, bgcolor: transparent"]None[/TD]
[TD="class: xl77, bgcolor: transparent"]None[/TD]
[TD="class: xl77, bgcolor: transparent"]None[/TD]
[TD="class: xl77, bgcolor: transparent"]None[/TD]
[TD="class: xl77, bgcolor: transparent"]None[/TD]
[/TR]
</tbody>[/TABLE]