worksheet formula like =minText(range) without vba and without sorting

Cells a2:a10 contain random text.  I want a formula that will give me the lowest value alphabetically.

This would be easy if the cells contained numbers. The formula would be  =min(a2:a10)

And, if the cells were sorted, this would be even easier. the formula would be =a2

But, I want the formula to work on text, and without sorting the table and without using vba or MS Query.

I don't think it is possible, but perhaps some expert can prove me wrong.

I have already been googling for a half hour, and many websites solve similar problems, but none of them work for me.  So please, before you point me to a website please verify that it actually works with the following list

yellow 8 inch bolts
z nuts aC
hex access bolt
hex Aa bolt  < === this would be the best answer
hex access bolt
hex aa bolt  < === but this would also be an acceptable answer
z z93 a
z d
z e
LVL 5
rberkeConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Michael FowlerSolutions ConsultantCommented:
Based on your example use the following

=INDEX(A1:A9,MATCH(MIN(COUNTIF(A1:A9,"<"&A1:A9)),COUNTIF(A1:A9,"<"&A1:A9),0))

This is an array formula so after entering this formula enter ctrl+shift+enter
example.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
Enter this is a cell replacing A1:A4 to match your data.
=INDEX(A1:A4,MATCH(MIN(COUNTIF(A1:A4,”<”&A1:A4)),COUNTIF(A1:A4,”<”&A1:A4),0))

The press control-shift-enter to create an array formula.
0
Martin LissOlder than dirtCommented:
Rats! Lost by 28 seconds.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

rberkeConsultantAuthor Commented:
Michael was correct  !!!! Michael used double quote around the "<" which is essential.
Martin was wrong  !!!! Martin used slanted double quotes around the < so it does NOT work.


Before posting here, I had independently tried the exact same formula that Martin used and my attempt failed for the exact same reason.  I even fiddled with the double quote issue, but I must have screwed up.   Anyway, I now know the secret so thanks to you both.
0
rberkeConsultantAuthor Commented:
The following demonstrates how the magic works.

Cells with the lowest value can be easily seen in  {4;7;2;0;2;0;8;5;6},

And, a 0 is perfectly logical.  When you have the lowest value from the list, there will be zero cells that are lower.

INDEX(   A1:A9,   MATCH(  MIN(COUNTIF(A1:A9,"<"&A1:A9)),    COUNTIF(A1:A9,"<"&A1:A9),0)     )
INDEX(   A1:A9,   MATCH(  0,                                {4;7;2;0;2;0;8;5;6},     0)     )
INDEX(   A1:A9,   6                                                                         )

Open in new window


The above also demonstrates why the following simplifications are reliable.

(avoid slanted quotes)
minimum text value in a range   =INDEX(A1:A9,MATCH(0,COUNTIF(A1:A9,"<"&A1:A9),0))
maximum text value in a range   =INDEX(A1:A9,MATCH(0,COUNTIF(A1:A9,">"&A1:A9),0))
0
rberkeConsultantAuthor Commented:
I went back to ExcelTip.com where I found the following

However, if there are blank cells in the range that appear before the true maximum then it will just return 0, so if you need to allow for blank cells then use the last formula shown below.
"=

(avoid slanted quotes and use control shift enter)
minimum text value in a range   =INDEX(A1:A9,MATCH(0,COUNTIF(A1:A9,"<"&A1:A9),0))
maximum text value in a range   =INDEX(A1:A9,MATCH(0,COUNTIF(A1:A9,">"&A1:A9),0))
maximum text ignoring blanks    =INDEX(A1:A9,MATCH(0,IF(A1:A9<>"",COUNTIF(A1:A9,">"&A1:A9)),0))
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.