Home

Index Match multiple results without duplicates

%3CLINGO-SUB%20id%3D%22lingo-sub-468983%22%20slang%3D%22en-US%22%3EIndex%20Match%20multiple%20results%20without%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-468983%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20can%20anyone%20help%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20get%20a%20cell%20to%20lookup%20ID2%20based%20on%20two%20criterias%20-%20Date%20%26amp%3B%20ID%20with%20the%20intention%20of%20having%20ID2%20returned%20without%20the%20duplicates.%20A%20simplified%20version%20of%20the%20layout%20is%20below%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EDate%3C%2FTD%3E%3CTD%3E1%2F04%2F2019%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EID%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EID2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDate%3C%2FTD%3E%3CTD%3EID%3C%2FTD%3E%3CTD%3EID2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F04%2F2019%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3EYEAR1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F04%2F2019%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3EYEAR1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F04%2F2019%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3EYEAR2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F04%2F2019%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3EYEAR1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F04%2F2019%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3EYEAR2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F04%2F2019%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3EYEAR1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F04%2F2019%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3EYEAR2%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20%3A)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-468983%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-469032%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20multiple%20results%20without%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-469032%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F323056%22%20target%3D%22_blank%22%3E%40El1-_321%3C%2FA%3E%20%2C%20it%20could%20be%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20688px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F109423i74077FDB33A82AC1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%3E%3DINDEX(%24C%246%3A%24C%2412%2CMATCH(1%2CINDEX((%24A%246%3A%24A%2412%3DB1)*(%24B%246%3A%24B%2412%3DB2)%2C0)%2C0))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-469036%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20multiple%20results%20without%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-469036%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-469244%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20multiple%20results%20without%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-469244%22%20slang%3D%22en-US%22%3EA%20single%20LOOKUP%20function%20can%20return%20the%20same%20result%2C%20like%20this%3A%3CBR%20%2F%3E%3DLOOKUP(2%2C%3CBR%20%2F%3E1%2F((A6%3AA12%3DB1)*%3CBR%20%2F%3E(B6%3AB12%3DB2))%2C%3CBR%20%2F%3EC6%3AC12)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-469266%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20multiple%20results%20without%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-469266%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-469274%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20multiple%20results%20without%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-469274%22%20slang%3D%22en-US%22%3EYou%E2%80%99re%20welcome.%3C%2FLINGO-BODY%3E
El1-_321
Occasional Contributor

Hi, can anyone help?

 

I am trying to get a cell to lookup ID2 based on two criterias - Date & ID with the intention of having ID2 returned without the duplicates. A simplified version of the layout is below: 

 

Date1/04/2019 
ID1 
ID2  
   
DateIDID2
1/04/20191YEAR1
1/04/20191YEAR1
1/04/20192YEAR2
1/04/20191YEAR1
1/04/20192YEAR2
1/04/20191YEAR1
1/04/20192YEAR2 

 

Thank you :)

5 Replies

@El1-_321 , it could be like this

image.png

=INDEX($C$6:$C$12,MATCH(1,INDEX(($A$6:$A$12=B1)*($B$6:$B$12=B2),0),0))
A single LOOKUP function can return the same result, like this:
=LOOKUP(2,
1/((A6:A12=B1)*
(B6:B12=B2)),
C6:C12)
You’re welcome.