Author: Oscar Cronquist Article last updated on November 21, 2018

Table of Contents

  1. Sort text from two columns combined (array formula)
  2. Sort text from multiple cell ranges combined (user defined function)

Array formula in cell F3:

=IFERROR(INDEX($D$3:$D$7, MATCH(ROWS(F$3:$F3)-1, COUNTIF($D$3:$D$7, "<"&$D$3:$D$7)+COUNTIF($B$3:$B$21, "<"&$D$3:$D$7)+IF((COUNTIF($D$3:$D$7, $D$3:$D$7)+COUNTIF($B$3:$B$21, $D$3:$D$7))<=COUNTIF($F$2:F2, $D$3:$D$7), 0, COUNTIF($F$2:F2, $D$3:$D$7)), 0)), INDEX($B$3:$B$21, MATCH(ROWS(F$3:$F3)-1, COUNTIF($B$3:$B$21, "<"&$B$3:$B$21)+COUNTIF($D$3:$D$7, "<"&$B$3:$B$21)+IF((COUNTIF($B$3:$B$21, $B$3:$B$21)+COUNTIF($D$3:$D$7, $B$3:$B$21))<=COUNTIF($F$2:F2, $B$3:$B$21), 0, COUNTIF($F$2:F2, $B$3:$B$21)), 0)))

This array formula handles duplicates but not blank cells.

How to create an array formula

  1. Select cell F3
  2. Copy/Paste above formula
  3. Press and hold Ctrl + Shift
  4. Press enter once
  5. Release all keys

How to copy array formula