lookup function?

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I have a one-column list of different text entries. I
want to return the text value that appears the most times
in the list.

Ex: List Entries include
Plates,Spoons,Plates,Knives,Glasses,Knives,Plates. I
want to return the value Plates because it appears the
most times in the list. Make sense? Which function
would I use for that???

Thanks in advance for the help.

Scott
 
I have a one-column list of different text entries. I
want to return the text value that appears the most times
in the list.
Click to expand...
...

Try the *array* formula

=INDEX(List,MATCH(MAX(COUNTIF(List,List)),COUNTIF(List,List),0))

Array formulas must be entered by holding down [Ctrl] and [Shift] keys before
pressing [Enter].
 
Hi Scott:

Not fully tested, but try:

=INDEX(A1:A13,MATCH(MAX(COUNTIF(A1:A13,A1:A13)),COUNTIF(A1:A13,A1:A13),0))

where your list is in A1:A13, entered as an array formula with <Ctrl>
<Shift> <Enter> rather than just <Enter>.

Regards,

Vasant.
 
Darn, Harlan ... it took me a long time to work that out and I was so proud
of it! :-)

Regards,

Vasant.

Harlan Grove said:
I have a one-column list of different text entries. I
want to return the text value that appears the most times
in the list.
Click to expand...
..

Try the *array* formula

=INDEX(List,MATCH(MAX(COUNTIF(List,List)),COUNTIF(List,List),0))

Array formulas must be entered by holding down [Ctrl] and [Shift] keys before
pressing [Enter].

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.
Click to expand...
 
Darn, Harlan ... it took me a long time to work that out and I was so proud
of it! :-)
Click to expand...
...

Who knows who posted first? (Well, it can be checked in the headers, but I won't
bother.) Anyway, it's almost certainly been posted before. Very few of the
methods in the responses in these newsgroups are original. If OPs ever did start
searching Google first, we'd be out of a job!
 
Anyway, it's almost certainly been posted before. Very few of the
methods in the responses in these newsgroups are original. If OPs ever did
start
searching Google first, we'd be out of a job!<<

Of course I had no expectation that my solution was original. Once in a
while I like to reinvent the wheel just to keep the brain exercised ...
especially since my "expertise" in worksheet functions is minimal!
 
Back
Top