# Getting array/index formula to ignore blank cells

1. ## Getting array/index formula to ignore blank cells

Hi,

I'm using this formula (entered as an array formula) to return unique values from a range in alphabetical order: =INDEX(\$B\$3:\$B\$11, MATCH(SMALL(IF(COUNTIF(\$D\$2:D2, \$B\$3:\$B\$11)=0, COUNTIF(\$B\$3:\$B\$11, "<"&\$B\$3:\$B\$11), ""), 1), COUNTIF(\$B\$3:\$B\$11, "<"&\$B\$3:\$B\$11), 0)).

The problem is that if there's a blank cell in the range, the formula goes crazy and displays the first (alphabetically speaking) value in all of the rows.

The problem seems to potentially come from this part: COUNTIF(\$D\$2:D2, \$B\$3:\$B\$11). When I use it by itself it returns 0 for all values not found in preceding rows of column D and 1 for all values that are found in precending rows (as it should), but when there's a blank cell in the range it returns values higher than 1 for the instances of the value that would come first in alphabetical order and 0 for all of the other values.

I figure there's probably a way to add the ifblank function somewhere in there that would work, but I don't know where.

Any help would be appreciated.

Simon  Register To Reply

2. ## Re: Getting array/index formula to ignore blank cells

For NEXT time, please remember thta it is MUCH easier for us if you provide a sample sheet to to work with.

This is the array formula that you need. You can adapt it to the ranges in your sheet.

=IFERROR(INDEX(\$A\$1:\$A\$20,MATCH(0,COUNTIF(\$A\$1:\$A\$20,"<"&\$A\$1:\$A\$20)+10^10*(\$A\$1:\$A\$20="")-SUM(COUNTIF(\$A\$1:\$A\$20,D\$1:D1)),0)),"")  Register To Reply

3. ## Re: Getting array/index formula to ignore blank cells

That works great. Thanks a lot!  Register To Reply

4. ## Re: Getting array/index formula to ignore blank cells

You're welcome.

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.  Register To Reply

5. ## Re: Getting array/index formula to ignore blank cells

Done. Thanks!  Register To Reply

6. ## Re: Getting array/index formula to ignore blank cells

Hi,Glenn,
How to solve this with two list data.
(Extract a unique distinct value with 2 list and sort alphabetically)  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 