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
  • Hi, thank you for your help so far. To clarify, a person will have the same phone number and reference number in columns B and D. If the reference number appears in both columns A and C, then we just need the phone number that appears in column B to appear in a new column. – Simon May 21 '13 at 12:01
  • 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
  • Thank you. This works perfectly. The next challenge is how would I be able to expand this formula on another spreadsheet with the same columns? On this one, I would like to do all the above (so i'm using the =IF(COUNTIF($A:$A,C1),B1,"") formula) I would like to add fields to Column E so that it also lists the telephone number that appears in Column B of any person's reference number (e.g. 00005) that appears in Column C but does not appear in Column A? – Simon May 21 '13 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.