Hi Biff!

I think Scott has the answer. However, if I try...

=MATCH(0,A1,0)

....where A1 contains 0, the formula returns 1 not #N/A even though the
second argument is a single value and not an array. So I don't quite
understand why.

In article ,
"Biff" wrote:

Hi Folks!

Trying to extract uniques from a list and make it dynamic. It works as long
as there are at least 2 entries in the list. If there is only 1 entry in the
list the formula errors but i don't understand why.

The formula without the error trap: (array)

=INDEX(List,MATCH(0,COUNTIF(A$1:A1,List),0))

List is a dynamic range name.

With only a single entry in List and A1 being empty, this is how the formula
evaluates:

=INDEX({1},MATCH(0,0,0))

Match returns #N/A when it should return 1 since 0 matches 0 and it's in the
first position of the lookup_array and the result of the entire formula
shoud be 1 since 1 is in the first position of the Index array.

Can anyone explain why this is not working?

Thanks

Biff