INDEX or extract list that doesn't Match to a range as criteria

**brake** · 11-16-2017, 09:55 AM

Hi,

I need help. How to index / extract data that doesn't match to a range as criteria to avoid and remove duplicate and blank data with formula.

indexNotMatch.JPG

I want in F2 and under shows unique data that doesn't match to criteria range (C2:C5) (No duplicate and no blank list).

the result should be like this :

indexNotMatchs.JPG

Sorry for my english and thank you for every help.

**modytrane** · 11-16-2017, 10:07 AM

what about Apple, Nectarine, Broad bean etc.....

**brake** · 11-16-2017, 10:17 AM

I'm so sorry....

the result should like this....

indexNotMatchs.JPG

thanks for your correction and advice

**MarvinP** · 11-16-2017, 11:02 AM

Hi brake,

Try using a helper column with your data that has a match function in it. Then do the advanced filter on the helper. See the attached:

Advanced Filter using Match for Not.xlsx

**brake** · 11-16-2017, 11:13 AM

Thank's for your suggestion. I need an array formula, cuz the data source contains thousand rows and the criteria for about 70 rows. and i want a unique value, removing the duplicate result

**63falcondude** · 11-16-2017, 11:19 AM

Try this in F2:

=IFERROR(INDEX(A$2:A$27,MATCH(1,COUNTIF(F$1:F1,A$2:A$27)+(COUNTIF(C$2:C$5,A$2:A$27)=0)*(A$2:A$27<>""),0)),"") Ctrl Shift Enter

Extend the ranges as far as needed.

**brake** · 11-16-2017, 11:31 AM

Originally Posted by 63falcondude

try this in f2:

=iferror(index(a$2:a$27,match(1,countif(f$1:f1,a$2:a$27)+(countif(c$2:c$5,a$2:a$27)=0)*(a$2:a$27<>""),0)),"") ctrl shift enter

extend the ranges as far as needed.

this's great n awesome. This is what i need.. I highly appreciate that. Thank's a million for your help.

**63falcondude** · 11-16-2017, 11:34 AM

You're welcome. Happy to help.

INDEX or extract list that doesn't Match to a range as criteria

LinkBack

Thread Tools

Rate This Thread

Display

INDEX or extract list that doesn't Match to a range as criteria

Re: INDEX or extract list that doesn't Match to a range as criteria

Re: INDEX or extract list that doesn't Match to a range as criteria

Re: INDEX or extract list that doesn't Match to a range as criteria

Re: INDEX or extract list that doesn't Match to a range as criteria

Re: INDEX or extract list that doesn't Match to a range as criteria

Re: INDEX or extract list that doesn't Match to a range as criteria

Re: INDEX or extract list that doesn't Match to a range as criteria

Thread Information

Users Browsing this Thread

Similar Threads

INDEX MATCH MATCH doesn't work well in a Filtered List?

[SOLVED] Extract duplicates from index/match BUT the criteria is three columns?

List Function with Criteria - Index Match with Criteria

[SOLVED] Multiple Criteria lookup to extract data from a dump - INDEX MATCH????

[SOLVED] Return address of next value in list which doesn't match criteria

[SOLVED] Index and Match To Extract All Unique Locations Meeting Reoccurring Criteria Value

List all scores which meet criteria (INDEX/MATCH?)

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions