CODEWITHSUNDEEP
excelindexingmatch
user9836688
user9836688

Reputation:

getting rid of the 0 from the middle of the list in index match return in excel

I have a similar to below list in excel column D:

Project Plan Sheet

And from the above list I want to get a list without the empty cells and with only names appearing once in cell J59 to down:

Output sheet:

I use the index match formula similar to below on the output sheet J59:

=INDEX('Project Plan'!D9:D100; MATCH(0; COUNTIF($J$58:J58;'Project Plan'!D9:D100);0))

When I dragged the formula down, I get such a result:

How can I get rid of this 0 from the middle of my list?

Upvotes: 0

Views: 97

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

Note that with the latest version of Excel, with the new FILTER and UNIQUE functions, you can accomplish this with:

=UNIQUE(FILTER(your_range,your_range<>""))

Upvotes: 0

Rory
Rory

Reputation: 34045

Use:

=INDEX('Project plan'!$D$9:$D$100; MATCH(0; COUNTIF($J$58:J58;'Project plan'!$D$9:$D$100&"");0))

Upvotes: 0

Related Questions