Search in a list.

C

comotoman

I have a list of 600 names and numbers. Is there a way to lookup a name
without having to spell all of it out? exp. i type in "jim" and get
"Jimbos Plumbing".
 
S

Smeeta Geary

You can if you put a filter on the colum by going to, Data, Auto Filter
(Press ALT, D, F, F)

Then filter as custom and select option 'Contains' from first drop dwon menu
and then type 'jim' in the find box to the right. This will filter all cells
that contains 'jim' in the cells
 
B

B. R.Ramachandran

Hi,

What did you mean my names and numbers - some entries are names and some
numbers, or the entries are alphanumeric?

Any way, if the names are in A2:A601 and the short name goes to B2, and you
want the result to go to C2, type in following formula in C2

=OFFSET(A2,MATCH(B2,LEFT($A$2:$A$601,LEN($B$2)),0)-1,0)

and confirm with Ctrl-Shift-Enter.

Now you can enter a short name in B2 and hit Enter; the formula should find
the full name. If the short name your enter corresponds to more than one
name in the list, it will find the first occurrence; so you have to use your
judgement to use a short name unique to each entry. The formula will return
#N/A if no match is found.

Regards,
B. R. Ramachandran
 
Top