#Formula small unique without array #

[daboho]

i wan use formula INDEX($B$4:$B$10;AGGREGATE(14;6;ROW($B$4:$B$10)/((MATCH($B$4:$B$10;$B$4:$B$10;0)));ROW(A1)))
to unique result without array CSE
how i can't do
ss1.png

[Glenn Kennedy]

Can you achieve that using AGGREGATE???

Here is a way that works, but it is an array formula:

=IFERROR(INDEX($B$4:$B$10,MATCH(0,COUNTIF($B$4:$B$10,"<"&$B$4:$B$10)-SUM(COUNTIF($B$4:$B$10,"="&D$3:D3)),0)),"")

Your location says England, but your formula uses ; instead of , as a separator... so you may need to change the , to ; - depending on your regional settings.

[daboho]

glenn kennedy what mean the formula : COUNTIF($B$4:$B$10,"<"&$B$4:$B$10)-SUM(COUNTIF($B$4:$B$10,"="&D$3:D3))
how to do in array can you explain

[Glenn Kennedy]

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...