Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

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

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,034
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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?


  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The table will likely be 5 columns.

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

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,034
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Works Great!

    You really are Da Man.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •