List only unique occurances in small function

[rhaney]

Good afternoon.

I have a formula which works correctly upon confirming with control+shift+enter, except that I need it to only list the unique values.

As it stands now, it will list all data in the range such as:

a
a
a
b
b
c
c
c
c

and I need it to list only unique occurrances of the data such as:

a
b
c

=IF(ISERROR(INDEX('Master store info'!$A$1:$AC$3000,SMALL(IF('Master store info'!$B$1:$B$3000=$F$5,ROW('Master store info'!$B$1:$B$3000)),ROW(1:1)),3)),"",INDEX('Master store info'!$A$1:$AC$3000,SMALL(IF('Master store info'!$B$1:$B$3000=$F$5,ROW('Master store info'!$B$1:$B$3000)),ROW(1:1)),3))

Thank you!

Robert

 

[water1355]

Thank Ron - I'm not the OP, but the regular formula (post 2007) solves my problem. If it's not too much trouble, can you tell us how it works? As near as I can figure, the inner index function is passing an array to the outer match function, but doing so without a CSE entry?

 

[Ron Coderre]

Thank Ron - I'm not the OP, but the regular formula (post 2007) solves my problem. If it's not too much trouble, can you tell us how it works? As near as I can figure, the inner index function is passing an array to the outer match function, but doing so without a CSE entry?

Actually, you've described exactly what the inner INDEX function does.
It converts an array that would typically need CTRL+SHIFT+ENTER into an implicit array that Excel can work with (without the C+S+E)