Frustration with an index/match query in Excel

0
votes

I'm throwing together a risk assessment/treatment Excel book, have built an index/match formula to pull the relevant 'Likelihood' & 'Seriousness' columns and provide the applicable score from a matrix on another sheet. The query appears to work fine for all but one column and I'm at a loss.

Any input would be greatly received.

The formula in question: =INDEX(Scoring!$E$5:$J$9,MATCH($C7,Scoring!$E$4:$J$4,0),MATCH($D7,Scoring!$D$5:$D$9,0))

Note please that this is from the specific #REF! failure as seen in the below screenshot, but all the column E cells are using the exact same formula; and it only goes to #REF! when I set the 'C' column to 'Almost Certain'.

Screenshot of excel workbook, with inset image of the scoring matrix for reference

1
excelexcel-formula

1 Answers

0
votes

Seems like you have mixed up the column and row reference within INDEX(). Try in E5:

=INDEX(Scoring!E$5:J$9,MATCH(D5,Scoring!D$5:D$9,0),MATCH(C5,Scoring!E$4:J$4,0))

Drag down.