Look up part of a name in a list of names.

[chadm]

I have a list of names:

a. jones
b. smith
r. adams

I want to look up these names in a table.

The names in the table are:

adam jones
brian smith
robert adams.

How can I do this? Should I use wild cards?

Thanks for any suggestions!

 

[Aladin Akyurek]

On 2002-04-19 12:42, chadm wrote:
I have a list of names:

a. jones
b. smith
r. adams

I want to look up these names in a table.

The names in the table are:

adam jones
brian smith
robert adams.

How can I do this? Should I use wild cards?

Thanks for any suggestions!

Is the table a 1- or 2-column table? And, what do you want to return when "a. jones" is found in the table?

 

[chadm]

The table will likely be 5 columns.

When the name is found, I want it to return the value one column over.

 

[Aladin Akyurek]

On 2002-04-19 13:01, chadm wrote:
The table will likely be 5 columns.

When the name is found, I want it to return the value one column over.

Apparently, "adam jones" in a single cell in the first column of your 5 column table. And your lookup values consist of an initial followed by a dot followed by a last name.

If so, supposing that your table is in B2:F100, insert a new column after next to column B.

In C2 enter and copy down as far as needed:

=LEFT(B2)&". "&RIGHT(B2,LEN(B2)-SEARCH(" ",B2))

Now you can use a straight VLOOKUP, for example:

=VLOOKUP(A1,C2:F100,2,0)

Aladin

 

[Mark W.]

On 2002-04-19 13:01, chadm wrote:
The table will likely be 5 columns.

When the name is found, I want it to return the value one column over.

=INDEX(table_range,MATCH(SUBSTITUTE(A1,". ","*"),INDEX(table_range,0,1),0),column_num)
This message was edited by Mark W. on 2002-04-19 13:29

 

[chadm]

Works Great!

You really are Da Man.