1. ## 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. ## 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. ## 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. ## Re: Vlookup (2003/2007)

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
•