VLOOKUP

[Sara]

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,

 

[T. Valko]

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 Edit>Copy
Then, Edit>Paste Special>Values>OK

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)

 

[Sara]

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]

Did you try the alternative to "fixing" the data at the very end of my
previous reply?