Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Vlookup (2003/2007)

    =IF(ISERROR(VLOOKUP($E7,Tater!$C$6:$Q$1222,8,FALSE )),"",VLOOKUP($E7,Tater!$C$6:$Q$1222,8,FALSE))
    I am using the above formula to look up names from a separate list. The formula above works fine if the names are an exact match.
    The names are formatted as---Doe,John Adam on the list I am referencing ''Tater!$C$6:$Q$1222". But the list I have to lookup FROM, I had to copy to excel from WORD and the names aren't identicle i.e. Doe, John or Doe, John A.

    Both lists do have the commas separating the last name from the First and Middle.
    Can I edit this formula to lookup under these conditions, or is there a better way?

  2. #2
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup (2003/2007)

    There are a couple of things you could do.
    If the list in Tater!C6:Q1222 were sorted (on C), you could use VLOOKUP with 1 as the last argument to find an approximate match.

    Another approach uses =TRIM(LEFT(TRIM($E7),FIND(CHAR(5),SUBSTITUTE(TRIM( $E7)," ",CHAR(5),2)&CHAR(5)))) to turn E7's "Smith, John A." or "Smith, John Adam" into "Smith, John" (It takes everything before the second space (after trimming the double spaces down to single spaces)

    Then =VLOOKUP(TRIM(LEFT(TRIM($E7),FIND(CHAR(5),SUBSTITU TE(TRIM($E7)," ",CHAR(5),2)&CHAR(5)))) & "*", Tater!$C$6:$Q$1222, 8, False) will return a match for the first entry that begins with "Smith, John". Note the use of the wildecard "*" in the VLOOKUP.

    It will match with "Smith, John" or "Smith, John A" or "Smith, John Bob" or "Smith, Johnson and Kline", which ever is first in Tater!C:C.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup (2003/2007)

    After applying your solutions, I saw that onel ist had sometimes omitted the first name and only used the middle name their were also some Doe,John Adam Sr lll type entries, in the other list.

    Your solution cut my problems down to about one third though and I greatly appreciate it.

    thanks

  4. #4
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup (2003/2007)

    Glad to have helped.
    This might help to wade through the remaining 1/3:
    Advanced Filter also will take pattern matching. If you have a two row, one column Criteria Range
    Name
    *John*

    It will show all rows where "John" is a sub-string of the entry in the column that has "Name" as its header.

Posting Permissions

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