Author: Oscar Cronquist Article last updated on January 01, 2019

Ralee asks in in this blog post: Sort values in parallel (array formula)

If there is information in adjacent columns, say:

Joel, AA, 7
Mark, FF, 31
Nick, AA, 7

with the possibility of matching sort criteria (columns 2 & 3), how would I display the corresponding data after the sort (sorting only columns 2 & 3) without repeats; that is,

Joel, AA, 7
Nick, AA, 7
Mark, FF, 31

Instead of:

Joel, AA, 7
Joel, AA, 7
Mark, FF, 31

?

Answer:

Array formula in B9:

=INDEX($B$3:$B$5, MATCH(SMALL(COUNTIF($C$3:$C$5, "<"&$C$3:$C$5)+(1/(COUNTIF($D$3:$D$5, ">"&$D$3:$D$5)+1+ROW($D$3:$D$5)/65536)), ROWS($A$1:A1)), COUNTIF($C$3:$C$5, "<"&$C$3:$C$5)+(1/(COUNTIF($D$3:$D$5, ">"&$D$3:$D$5)+1+ROW($D$3:$D$5)/65536)), 0))

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Array formula in C9:

=INDEX($C$3:$C$5, MATCH(SMALL(COUNTIF($C$3:$C$5, "<"&$C$3:$C$5), ROWS($A$1:A1)), COUNTIF($C$3:$C$5, "<"&$C$3:$C$5), 0))

Array formula in D9:

=INDEX($D$3:$D$5, MATCH(SMALL(COUNTIF($C$3:$C$5, "<"&$C$3:$C$5)+(1/(COUNTIF($D$3:$D$5, ">"&$D$3:$D$5)+1)), ROWS($A$1:A1)), COUNTIF($C$3:$C$5, "<"&$C$3:$C$5)+(1/(COUNTIF($D$3:$D$5, ">"&$D$3:$D$5)+1)), 0))

Explaining array formula in cell B9