A few days ago I solved a problem that I’ve been working on for more than five years: How to create a multi-cell array formula that returns a sorted list of text entries. It’s easy enough to do this with numeric entries, but the text solution has eluded me.

Range A2:A21 is named *data*. Select C2:C21 and type (or paste) the formula below. Then press Ctrl+Shift+Enter (rather than Enter).

Text entries made in the *data* range appear in the C2:C21 range in alphabetical order.

Caveat: It doesn’t work for numeric entries, or duplicated text entries.

If you’d like to get rid of the #N/A display, wrap it up in an IF function:

Here’s how to do it using the new IFERROR function (and this may be the best reason yet to upgrade to Excel 2007):

Why did it take me so long to figure this out? I was fixated on an entirely different approach — one that I’m convinced would never work. I hadn’t thought about it for about a year, but then all of a sudden it came to me.

I can’t think of any good reason to actually use this, but I feel pretty good about figuring it out.