# How do I create a list of phone numbers for people whose reference numbers appear in two columns?

I have two sets of data in columns A & B and also in columns C & D. Some of the content in column C & D does not appear in columns A & B:

`A = reference numbers`
`00001, 00002, 00003, 00004`

`B = phone numbers for column a people`
`123456789, 234567890, 345678901, 456789012`

`C = reference numbers` (these contain SOME of the reference numbers that appear in column A)
`00001, 00002, 00004`
(NOTE: 00003 is missing)

`D = phone numbers for column C people` (these contain SOME of the phone numbers that appear in column B)
`123456789, 234567890, 456789012`
(NOTE: the phone number 345678901 is missing)

How would I create a separate column that contains the phone number from column B of anyone whose reference numbers appear anywhere in BOTH columns A and C?

-

In column E, enter `=IF(COUNTIF(\$A:\$A,C1),B1,"")`. Then drag this formula down as far as you need.
What this is doing is counting the instances of the reference number in column `C` against the entire column `A`. If it finds one or more instances, it outputs the phone number found in column `D`, else it displays a blank.
You can then apply a filter on column `E`, uncheck blanks, copy the column, remove filter and paste in column `F` to get a continuous list without blanks (and after this can safely delete column `E`).
The way I read the question, it sounds like they want the result to be both `B` & `D` phone numbers together. – CharlieRB May 21 '13 at 11:46
I had thought of that possibility, but in the example they give, the phone numbers correspond to the reference numbers. So I assume ref 0001 will have the same phone number in either column `B` or `D`. But if it is the other way around Simon, please comment :) Also mention how you would like the two numbers split, i.e. comma, or space, etc – Amer May 21 '13 at 11:51
Ok, so my original formula stands, but made an edit to the answer to change the `D1` to `B1`. Though it should not make a difference according to your rules. – Amer May 21 '13 at 12:19