SOLVED

Match query giving odd answers

%3CLINGO-SUB%20id%3D%22lingo-sub-1222097%22%20slang%3D%22en-US%22%3EMatch%20query%20giving%20odd%20answers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1222097%22%20slang%3D%22en-US%22%3E%3CP%3EAve%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20got%20a%20workbook%20with%205%20sheets.%20One%20of%20which%20is%20a%20scoring%20sheet%20with%20a%20grid%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ALMIS_Rob_0-1583933223836.png%22%20style%3D%22width%3A%20436px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F176348iF5A5CCB81B9DA368%2Fimage-dimensions%2F436x131%3Fv%3D1.0%22%20width%3D%22436%22%20height%3D%22131%22%20title%3D%22ALMIS_Rob_0-1583933223836.png%22%20alt%3D%22ALMIS_Rob_0-1583933223836.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20other%20sheets%2C%20I've%20got%20tables%20much%20like%20the%20following%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ALMIS_Rob_1-1583933329862.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F176349iB03785BBF1422DDC%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22ALMIS_Rob_1-1583933329862.png%22%20alt%3D%22ALMIS_Rob_1-1583933329862.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20in%20the%20'Rank'%20column%20I've%20got%20the%20following%20query%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%3DINDEX(Scoring!%24D%244%3A%24J%249%2CMATCH(%24C5%2CScoring!%24E%244%3A%24J%244)%2CMATCH(%24D5%2CScoring!%24D%245%3A%24D%249))%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EWhich%20only%20kind%20of%20works.%20For%20some%20combinations%20it%20gives%20me%20a%20figure%2C%20but%20as%20you%20can%20see%20from%20the%20screenshot%20above%2C%20%22Remote%20%22%20%26amp%3B%20%22Negligible%22%20should%20give%20me%20a%20Rank%20score%20of%201.%20Not%2020!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESome%20combinations%20of%20Occurrence%2FSeriousness%20give%20me%20an%20%22%23N%2FA%22%20error.%20I%20should%20note%20that%20the%20Occurrence%20%26amp%3B%20Seriousness%20columns%20are%20both%20drop%20down's%20validating%20against%20the%20column%20and%20row%20from%20the%20Scoring%20sheet%2C%20so%20there's%20no%20typographic%20type%20errors%20creeping%20in.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20scratching%20my%20head%20a%20little%20here.%20Possibly%20a%20woods%2Ftrees%20moment.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1222097%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1222265%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20query%20giving%20odd%20answers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1222265%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F319374%22%20target%3D%22_blank%22%3E%40ALMIS_Rob%3C%2FA%3E%26nbsp%3BTry%20changing%20your%20INDEX%20range%20to%26nbsp%3B%3CSPAN%3E%24E%245%3A%24J%249%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1222298%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20query%20giving%20odd%20answers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1222298%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20just%20gives%20me%20a%20'%23REF!'%20error%20-%20it's%20my%20understanding%20that%20the%20index%20range%20needs%20to%20include%20what%20you're%20using%20as%20search%20reference%3F%20(in%20this%20case%20the%20D%20column%20with%20the%20headers%20for%20Consequence%20(Neg%2C%20Minor%2C%20Moderate%2C%20etc)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1222323%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20query%20giving%20odd%20answers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1222323%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F319374%22%20target%3D%22_blank%22%3E%40ALMIS_Rob%3C%2FA%3E%26nbsp%3BYour%20index%20range%20is%206%20rows%20by%207%20columns%2C%20where%20your%20match%20will%20pick%201%20to%205%20rows%20and%201%20to%206%20columns.%20%26nbsp%3BIn%20your%20example%20Remote%2FNegligible%20returns%20row%201%20column%201%2C%20which%20is%20a%20blank%20cell.%20Don't%20know%20why%20it%20returned%2020%20to%20begin%20with.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1222354%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20query%20giving%20odd%20answers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1222354%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20even%20with%20the%20index%20range%20changed%20to%20%24E%245%20instead%20of%20%24D%244%20-%20it's%20still%20broken%2C%20but%20for%20a%20different%20reason%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBother.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1222366%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20query%20giving%20odd%20answers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1222366%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20just%20worked%20out%20what%20I%20was%20missing.%20For%20each%20of%20the%20Match%20statements%20(MATCH(%24C5%2CScoring!%24E%244%3A%24J%244)%20I%20was%20missing%20a%20return%20context%20on%20the%20end%2C%20in%20this%20case%20I've%20just%20put%20an%20extra%20comma%2C%20and%20now%20it%20works%20fine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX(Scoring!%24E%245%3A%24J%249%2CMATCH(%24C5%2CScoring!%24E%244%3A%24J%244%2C)%2CMATCH(%24D5%2CScoring!%24D%245%3A%24D%249%2C))%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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 (Occasional 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,))