Index Match Returning Duplicates Against Multiple Conditions

%3CLINGO-SUB%20id%3D%22lingo-sub-2803268%22%20slang%3D%22en-US%22%3EIndex%20Match%20Returning%20Duplicates%20Against%20Multiple%20Conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2803268%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20running%20a%20new%20task%20for%20reporting%20and%20am%20unable%20to%20get%20a%20unique%20list%20of%20results%20based%20on%20multiple%20conditions.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20result%20I'm%20expecting%20is%20a%20unique%20list%20of%20IDs%20ordered%20by%20the%20highest%20number%20of%20orders%20in-between%2010%20and%2020.%20Any%20help%20would%20be%20much%20appreciated!%3CBR%20%2F%3E%3CBR%20%2F%3E%3DINDEX(Sales_Data!%24D%3A%24D%2C%20MATCH(1%2C(IF(Sales_Data!%24H%3A%24H%26gt%3B10%2CIF(Sales_Data!%24H%3A%24H%26lt%3B21%2CIF(Sales_Data!%24T%3A%24T%3D%24C%245%2CIF(Sales_Datas!%24A%3A%24A%3D%24G%245%2CSales_Data!%24M%3A%24M))))%3DLARGE(IF(Sales_Data!%24H%3A%24H%26gt%3B10%2CIF(Sales_Data!%24H%3A%24H%26lt%3B21%2CIF(RSales_Data!%24T%3A%24T%3D%24C%245%2CIF(Sales_Data!%24A%3A%24A%3D%24G%245%2CSales_Data!%24M%3A%24M))))%2C'Monthly%20Report'!%24B124))*(COUNTIF(C%24123%3AC123%2C%20Sales_Data!%24D%3A%24D)%3D0)%2C%200))%3CBR%20%2F%3E%3CBR%20%2F%3EHowever%2C%20some%20of%20these%20IDs%20are%20repeated%20month%20on%20month.%3CBR%20%2F%3E%3CBR%20%2F%3EI'd%20equally%20accept%20an%20alternate%20solution!%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2803268%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-2803707%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20Returning%20Duplicates%20Against%20Multiple%20Conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2803707%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1172447%22%20target%3D%22_blank%22%3E%40yeeyee%3C%2FA%3E%26nbsp%3BOK%20per%20the%20advice%20of%20the%20pinned%20message%20in%20this%20forum%20it%20is%20very%20helpful%20to%20include%20what%20version%20of%20excel%20you%20are%20using%20and%20a%20sample%20file%2Fworksheet%20with%20the%20data%20you%20have%20and%20what%20you%20want.%26nbsp%3B%20This%20formula%20is%20very%20hard%20to%20put%20into%20context%20but%20I%20do%20notice%20some%20things%20that%20might%20help.%3C%2FP%3E%3CP%3EFirst%20off%2C%20if%20you%20have%20excel%20365%20then%20you%20have%20access%20to%20new%20functions%20like%20UNIQUE%20and%20FILTER%20and%20SORT%20and%20LET%20which%20can%20make%20this%20much%20easier.%26nbsp%3B%3C%2FP%3E%3CP%3ESecond%2C%20you%20could%20and%20should%20at%20least%20use%20the%20AND()%20function%20or%20multiply%20the%20conditionals%20instead%20of%20all%20those%20nested%20IF()%3C%2FP%3E%3CP%3EThird%2C%20your%20formula%20is%20using%20FIXED%20references%20in%20almost%20every%20case%20meaning%20if%20you%20copy%20this%20formula%20down%20it%20will%20not%20change%20that%20reference.%26nbsp%3B%20In%20some%20cases%20that%20is%20what%20you%20want%20but%20in%20this%20type%20of%20formula%20I%20suspect%20the%20references%20to%20%24C%245%20and%20%24G%245%20are%20really%20supposed%20to%20be%20C5%20and%20G5%20(or%20could%20be%20%24C5%20and%20%24G5)%20so%20when%20you%20copy%20down%20each%20cell%20will%20reference%20a%20different%20corresponding%20cell%20(e.g.%20on%20the%20same%20row%20but%20in%20columns%20C%20and%20G).%26nbsp%3B%20That%20said%2C%20this%20section%20of%20the%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3CSPAN%3E'Monthly%20Report'!%24B124))*(COUNTIF(C%24123%3AC123%2C%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eis%20using%20RELATIVE%20references%20so%20they%20will%20change%20as%20you%20copy%20down%20and%20I'm%20not%20sure%20if%20you%20want%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eagain%20it%20is%20really%20hard%20for%20me%20to%20know%20what%20is%20or%20isn't%20wrong%20without%20the%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi there,

I am running a new task for reporting and am unable to get a unique list of results based on multiple conditions. 

The result I'm expecting is a unique list of IDs ordered by the highest number of orders in-between 10 and 20. Any help would be much appreciated!

=INDEX(Sales_Data!$D:$D, MATCH(1,(IF(Sales_Data!$H:$H>10,IF(Sales_Data!$H:$H<21,IF(Sales_Data!$T:$T=$C$5,IF(Sales_Datas!$A:$A=$G$5,Sales_Data!$M:$M))))=LARGE(IF(Sales_Data!$H:$H>10,IF(Sales_Data!$H:$H<21,IF(RSales_Data!$T:$T=$C$5,IF(Sales_Data!$A:$A=$G$5,Sales_Data!$M:$M)))),'Monthly Report'!$B124))*(COUNTIF(C$123:C123, Sales_Data!$D:$D)=0), 0))

However, some of these IDs are repeated month on month.

I'd equally accept an alternate solution!

Thank you

1 Reply

@yeeyee OK per the advice of the pinned message in this forum it is very helpful to include what version of excel you are using and a sample file/worksheet with the data you have and what you want.  This formula is very hard to put into context but I do notice some things that might help.

First off, if you have excel 365 then you have access to new functions like UNIQUE and FILTER and SORT and LET which can make this much easier. 

Second, you could and should at least use the AND() function or multiply the conditionals instead of all those nested IF()

Third, your formula is using FIXED references in almost every case meaning if you copy this formula down it will not change that reference.  In some cases that is what you want but in this type of formula I suspect the references to $C$5 and $G$5 are really supposed to be C5 and G5 (or could be $C5 and $G5) so when you copy down each cell will reference a different corresponding cell (e.g. on the same row but in columns C and G).  That said, this section of the formula:

       'Monthly Report'!$B124))*(COUNTIF(C$123:C123, 

is using RELATIVE references so they will change as you copy down and I'm not sure if you want that.

 

again it is really hard for me to know what is or isn't wrong without the sheet.