Match values in one column and return values from another

**chunkysoup56** · 08-26-2010, 03:48 PM

Hello, this is my first post.

Here is my problem.... I have 3 colums; A, B and C.

A= Last and First names of select employees

B= Last and First names of entire company employees

C= Phone number for entire company employees (matching column B)

I'm trying to marry up the phone numbers from listed in column C to column A without doing cuttting and pasting them one by one.... any suggestions? Thanks in advance

**Paul** · 08-26-2010, 06:11 PM

Hi Chunky, welcome to the forum.

I've edited your thread title to something descriptive of your problem. Please try to create better thread titles in the future.

If your selected employees are in A1:A20, your full list of employees in B1:B100 and your full list of phone numbers in C1:C100... where should the selected employee phone numbers go? D1:D20?

If so, in D1 use the formula:

=VLOOKUP(A1,$B$1:$C$100,2,0)

Fill that down to D20 and you're all set.

**chunkysoup56** · 08-27-2010, 09:09 AM

thanks so much!

**chunkysoup56** · 08-27-2010, 10:35 AM

is there a way to combat name variances that are creating a "#N/A" return value?

i.e. column A lists an employees name as: Smith, John and column B lists the same name with middle name: Smith, Brian John

**Paul** · 08-27-2010, 11:18 AM

To be able to somehow match "Smith, Brian" with "Smith, Brian John" can likely be done, but usually when dealing with lists of names there are always going to be exceptions - and adding formula to account for those exceptions can get tricky and/or lengthy. To just hide the #N/A error altogether, you could use:

=IFERROR(VLOOKUP(A1,$B$1:$C$100,2,0),"")

Note: This works in Excel 2007+, not 2003-. In 2003- you would use:

=IF(ISERROR(VLOOKUP(A1,$B$1:$C$100,2,0)),"",VLOOKUP(A1,$B$1:$C$100,2,0))