Index and Match function

[Geo Jul]

Dear All,
I am facing a problem with this formula I usually use this formula: =INDEX($B$2:$B$127,MATCH(0,COUNTIF($J$15:J15,$B$2:$B$127),0),1)
to extract non-duplicate items from a list, know that some cells are empty so it won't work
Your assistance in this subject is highly appreciated

Regards

 

[Fluff]

How about

=unique(filter($B$2:$B$127,$B$2:$B$127<>""))

 

[Geo Jul]

How about

=unique(filter($B$2:$B$127,$B$2:$B$127<>""))

Dear All,
I am facing a problem with this formula I usually use this formula: =INDEX($B$2:$B$127,MATCH(0,COUNTIF($J$15:J15,$B$2:$B$127),0),1)
to extract non-duplicate items from a list, know that some cells are empty so it won't work
Your assistance in this subject is highly appreciated

Regards

please note that I am using excel 2016 at work

 

[Geo Jul]

How about

=unique(filter($B$2:$B$127,$B$2:$B$127<>""))

many thanks on your solution but at work we use excel 2010 and excel 2016 That is why i am using the above formula thank you

 

[Fluff]

It would have helped if you either said that, or amend you profile to show 2016 rather than 365.

 

[Fluff]

How about

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$127)/($B$2:$B$127<>"")/(ISNA(MATCH($B$2:$B$127,J$15:J15,0))),1)),"")

 

[Geo Jul]

It would have helped if you either said that, or amend you profile to show 2016 rather than 365.

i will thank you

 

[Geo Jul]

How about

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$127)/($B$2:$B$127<>"")/(ISNA(MATCH($B$2:$B$127,J$15:J15,0))),1)),"")

Many thanks for your solution and your time

 

[Geo Jul]

Many thanks for your solution and your time

How about

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$127)/($B$2:$B$127<>"")/(ISNA(MATCH($B$2:$B$127,J$15:J15,0))),1)),"")

is it possible for your formula to be used on two columns instead of one?

 

[Fluff]

Please post some sample data & expected results.