match and paste updated info in worksheets

  • Thread starter Thread starter susan
  • Start date Start date
S

susan

I have 2 worksheets heading colunms exactly the same,
listing names, addresses, phone numbers in rows. Each
person has a unique ID number.
If the person is on both worksheets the unique ID# is the
same. 1 worksheet with phone numbers and 1 worksheet W/Out
phone numbers. I am trying to come up with an easy way to
update my worksheet with the phone numbers from the other -
I need to match only the people in my worksheet.

can you help me with an easy way that will update my
worksheet with a match using the unique ID numbers to
automatically paste the phone numbers in where there is no
phone for that unique ID#?
 
I think you should try a VLOOKUP. You will enter this
function into the cells where you want data to show up. I
suggest you sort your sheet so that all names without
phone numbers are together so you can just copy the
formula down the column.

Lets say both sheets have the ID# in column A and the
phone# column is C. Assuming you are entering this
function into cell C2 on the NEED PHONE# sheet, your
formula would look like:

=VLOOKUP(A2, PHONESHEET!$A$2:$C$100, 3, FALSE)

Then, you can delete all the answers that come back #N/A:
that means the ID# wasn't on the other sheet.

If you want to get real fancy:

=IF(ISERROR(VLOOKUP(A2, PHONESHEET!$A$2:$C$100, 3,
FALSE)),"",VLOOKUP(A2, PHONESHEET!$A$2:$C$100, 3, FALSE))

This will give you blanks instead of #N/A.

Hope this works for you!

Erin Kotch
 
Thank YOU! That works great.
-----Original Message-----
I think you should try a VLOOKUP. You will enter this
function into the cells where you want data to show up. I
suggest you sort your sheet so that all names without
phone numbers are together so you can just copy the
formula down the column.

Lets say both sheets have the ID# in column A and the
phone# column is C. Assuming you are entering this
function into cell C2 on the NEED PHONE# sheet, your
formula would look like:

=VLOOKUP(A2, PHONESHEET!$A$2:$C$100, 3, FALSE)

Then, you can delete all the answers that come back #N/A:
that means the ID# wasn't on the other sheet.

If you want to get real fancy:

=IF(ISERROR(VLOOKUP(A2, PHONESHEET!$A$2:$C$100, 3,
FALSE)),"",VLOOKUP(A2, PHONESHEET!$A$2:$C$100, 3, FALSE))

This will give you blanks instead of #N/A.

Hope this works for you!

Erin Kotch


.
Click to expand...
 
Back
Top