Got it!
=INDEX(List,MATCH(0,INDEX(COUNTIF(A$1:A1,List),,1) ,0))
Biff
"Biff" wrote in message
...
Thanks for taking a look, Domenic!
I don't like not understanding things.
Biff
"Domenic" wrote in message
...
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
|