Unfortunately my data is fairly large and it would be difficult to fix. Is
there a way to do the VLookUp with a wildcard character or anything?
Thanks.

"T. Valko" wrote:

I'd fix it so that the names are in the same format.

For example, in sheet 2 I'd insert a new column B then do Text to Columns on
column A using comma delimiter.

You'll end up with this:

...........A..........B
1.....Jones.....Sara

Then, I'd insert a new column A and enter this formula in A1:

=C1& &B1

You'll end up with this:

................A...............B.............C
1.......Sara Jones....Jones........Sara

Then I'd convert A1 to a constant:

Select A1
Goto EditCopy
Then, EditPaste SpecialValuesOK

Then I'd delete columns B & C.

Then you can use a *simple* VLOOKUP formula to get the desired result.

Or...

Assuming the names are *always* 2 word names:

A2 = Sara Jones
Sheet2 A2 = Jones, Sara
Sheet2 B2 = Brown

=VLOOKUP(MID(A2&", "&A2,FIND(" ",A2)+1,LEN(A2)+1),Sheet2!A:B,2,0)

--
Biff
Microsoft Excel MVP


"Sara" wrote in message
...
Hello,

I am trying to match a name on one spreadsheet with a name on another
spreadsheet to receive a resulting contact.

For example on sheet one the following is shown:
A
Client Name

Sara Jones

On Sheet two the following information is shown:
A B
Name CPA Name
Jones, Sara Brown

The two sheets have a corresponding name, but they are not exact. Is
there
a way to do a Vlookup for the name (even though they are not they same)
and
receive the corresponding CPA name?

Thanks,