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
Bookmarks