Forum Discussion
Match query giving odd answers
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,))
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.
So even with the index range changed to $E$5 instead of $D$4 - it's still broken, but for a different reason?
Bother.
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,))