Finding words from a list in Excel?

Question

I have a list of 30000 names in the form johnsmith and I know if I wanted to flag all the cells with "johnsmith" I could write: =(VLOOKUP(A1,names,1,FALSE),A1,1), where A1 is the what I'm testing and names is the table of names that I want to key in on. But what if I wanted all names with smith? I assume I need something like: FIND(VLOOKUP(A1,test,1,FALSE),A1,1), but it won't work. *Note the names are in the form johnsmith, so with no space I have nothing to parse on, so I will need some kind of find function.

Answer 1

If you want to find the number of names ended with "smith" in a table, use:
=COUNTIF( A:A, "*smith")

If you want to get the rows that ended with "smith" in a table into a new table, that is another issue
Use Data > Filter > Advanced Filter
it is like the description i posted here earlier
http://answers.yahoo.com/question/index;_ylt=AvqY1XRod1xrHjLV8iwDPmzsy6IX?qid=20070222204702AAZOtrM&show=7#profile-info-S0prXGsQaa
use it and see how wonderfull Excel is

Enjoy my profile, I am the VBAXLMan

Answer 2

It is easier to filter all the smith in place.
Select autofilter, select custom, select 'containing' type 'smith'