SOLVED

Match query giving odd answers

Brass Contributor

Ave,

 

I've got a workbook with 5 sheets. One of which is a scoring sheet with a grid:

ALMIS_Rob_0-1583933223836.png

 

On the other sheets, I've got tables much like the following:

ALMIS_Rob_1-1583933329862.png

 

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.

5 Replies

@ALMIS_Rob Try changing your INDEX range to $E$5:$J$9

@Riny_van_Eekelen 

 

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)

@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.

@Riny_van_Eekelen 

 

So even with the index range changed to $E$5 instead of $D$4 - it's still broken, but for a different reason?

 

Bother.

best response confirmed by ALMIS_Rob (Brass Contributor)
Solution

@Riny_van_Eekelen 

 

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,))

1 best response

Accepted Solutions
best response confirmed by ALMIS_Rob (Brass Contributor)
Solution

@Riny_van_Eekelen 

 

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,))

View solution in original post