VLOOKUP
 

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,

VLOOKUP
 

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,

VLOOKUP
 

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,

VLOOKUP
 

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

--
Biff
Microsoft Excel MVP


"Sara" wrote in message
...
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,