SOLVED
Home

Find values in a two dim array that are not also in another column

%3CLINGO-SUB%20id%3D%22lingo-sub-536249%22%20slang%3D%22en-US%22%3EFind%20values%20in%20a%20two%20dim%20array%20that%20are%20not%20also%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-536249%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20two%20dimensional%20array%20%24s%245%3A%24v%2414%20that%20contains%20numbers%20and%20blanks%20derived%20from%20formulas%20within%3C%2FP%3E%3CP%3EIn%20%24w%245%3A%24w%2414%20are%20numbers%3C%2FP%3E%3CP%3EI%20want%20in%20col%20%24X%20to%20list%20the%20numbers%20from%20%24w%245%3A%24w%2414%20that%20are%20not%20in%20the%20array%20%24S%245%3A%24v%2414%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-536249%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-536461%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20values%20in%20a%20two%20dim%20array%20that%20are%20not%20also%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-536461%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322032%22%20target%3D%22_blank%22%3E%40Dichotomy66%3C%2FA%3E%26nbsp%3B%2C%20if%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%20385px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F112370iBBA6F1456567AB7E%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%3CP%3Ewhen%20in%20X5%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(%24W%245%3A%24W%2414%2CAGGREGATE(15%2C6%2C1%2F(COUNTIF(%24S%245%3A%24V%2414%2C%24W%245%3A%24W%2414)%3D0)*(ROW(%24W%245%3A%24W%2414)-ROW(%24W%244))%2C(ROW()-ROW(%24X%244))%20))%2C%22%22)%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20down%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-536473%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20values%20in%20a%20two%20dim%20array%20that%20are%20not%20also%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-536473%22%20slang%3D%22en-US%22%3EThanks%20this%20worked%20great%20and%20was%20easy%20to%20adapt%20to%20other%20with%20similar%20situation%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-536549%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20values%20in%20a%20two%20dim%20array%20that%20are%20not%20also%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-536549%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322032%22%20target%3D%22_blank%22%3E%40Dichotomy66%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Dichotomy66
Contributor

I have a two dimensional array $s$5:$v$14 that contains numbers and blanks derived from formulas within

In $w$5:$w$14 are numbers

I want in col $X to list the numbers from $w$5:$w$14 that are not in the array $S$5:$v$14

3 Replies
Solution

@Dichotomy66 , if like this

image.png

when in X5

=IFERROR(INDEX($W$5:$W$14,AGGREGATE(15,6,1/(COUNTIF($S$5:$V$14,$W$5:$W$14)=0)*(ROW($W$5:$W$14)-ROW($W$4)),(ROW()-ROW($X$4)) )),"")

and drag it down

Thanks this worked great and was easy to adapt to other with similar situation
Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies