INDEX MATCH not recognising column

[AlisonG]

Hello,

I'm trying to use Excel for a risk assessment where users will select the likelihood and consequence of something occurring, and the spreadsheet will then provide the rating and a value attached to it.

I am using dropdowns for the likelihood and consequence, then an INDEX MATCH formula for the rating, which is partly working, but is not recognising one column from my risk table and I can't work out why.

My formula is: =INDEX(RiskTables!$C$3:$E$5,MATCH(C88,RiskTables!$B$3:$B$5,0),MATCH(D88,RiskTables!$C$2:$E$2,0))

My risk table is very simple:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Low[/TD]
[TD]Medium[/TD]
[TD]High[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Unlikely[/TD]
[TD]Low[/TD]
[TD]Low[/TD]
[TD]Moderate[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Possible[/TD]
[TD]Low[/TD]
[TD]Moderate[/TD]
[TD]High[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Likely[/TD]
[TD]Moderate[/TD]
[TD]High[/TD]
[TD]Extreme[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

But every time the user selects a consequence of High the Rating doesn't work and just remains a #N/A

[TABLE="width: 341"]
<tbody>[TR]
[TD]Likelihood[/TD]
[TD]Consequence[/TD]
[TD]Rating[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Unlikely[/TD]
[TD]Low[/TD]
[TD]Low[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Possible[/TD]
[TD]Low[/TD]
[TD]Low[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Unlikely[/TD]
[TD]High[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Possible[/TD]
[TD]High[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Likely[/TD]
[TD]High[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any ideas what might be going on here?

I'm using Windows 8 Excel 2013.

Thanks!

 

[StephenCrump]

Welcome to the Forum!

I suggest you check for spaces, i.e. that you're matching "High" with "High", rather than "High " or " High".

 

[L. Howard]

Check for a leading or trailing space in the cell E2. If none, then look in the drop down list source window, if the list is referred to in a range on the sheet, look at the cell there that has High in for a space. If the three words are typed in the source window, there could be a trailing space there.

Howard

 

[AlisonG]

That was it! Thanks so much, so simple.