Mar 11 2020 06:34 AM
Ave,
I've got a workbook with 5 sheets. One of which is a scoring sheet with a grid:
On the other sheets, I've got tables much like the following:
And in the 'Rank' column I've got the following query:
=INDEX(Scoring!$D$4:$J$9,MATCH($C5,Scoring!$E$4:$J$4),MATCH($D5,Scoring!$D$5:$D$9))
Which only kind of works. For some combinations it gives me a figure, but as you can see from the screenshot above, "Remote " & "Negligible" should give me a Rank score of 1. Not 20!
Some combinations of Occurrence/Seriousness give me an "#N/A" error. I should note that the Occurrence & Seriousness columns are both drop down's validating against the column and row from the Scoring sheet, so there's no typographic type errors creeping in.
I'm scratching my head a little here. Possibly a woods/trees moment.
Mar 11 2020 07:29 AM
@ALMIS_Rob Try changing your INDEX range to $E$5:$J$9
Mar 11 2020 07:35 AM
That just gives me a '#REF!' error - it's my understanding that the index range needs to include what you're using as search reference? (in this case the D column with the headers for Consequence (Neg, Minor, Moderate, etc)
Mar 11 2020 07:41 AM - edited Mar 11 2020 07:57 AM
@ALMIS_Rob Your index range is 6 rows by 7 columns, where your match will pick 1 to 5 rows and 1 to 6 columns. In your example Remote/Negligible returns row 1 column 1, which is a blank cell. Don't know why it returned 20 to begin with.
Mar 11 2020 07:58 AM
So even with the index range changed to $E$5 instead of $D$4 - it's still broken, but for a different reason?
Bother.
Mar 11 2020 08:03 AM - edited Mar 11 2020 08:04 AM
Solution
I've just worked out what I was missing. For each of the Match statements (MATCH($C5,Scoring!$E$4:$J$4) I was missing a return context on the end, in this case I've just put an extra comma, and now it works fine.
=INDEX(Scoring!$E$5:$J$9,MATCH($C5,Scoring!$E$4:$J$4,),MATCH($D5,Scoring!$D$5:$D$9,))
Mar 11 2020 08:03 AM - edited Mar 11 2020 08:04 AM
Solution
I've just worked out what I was missing. For each of the Match statements (MATCH($C5,Scoring!$E$4:$J$4) I was missing a return context on the end, in this case I've just put an extra comma, and now it works fine.
=INDEX(Scoring!$E$5:$J$9,MATCH($C5,Scoring!$E$4:$J$4,),MATCH($D5,Scoring!$D$5:$D$9,))