Excel formula if a number in cell B2 matches any other number in Columns C:D and if so ret

I am working with a file that has several hundred thousand ID numbers, and I am trying to match phone numbers to make sure I don't have the same number listed more than once in several different columns. If it in face I do have the same phone listed more than once I would like it to return the ID # of the location of the multiple listing. any help would be appreciated

I think the quickest way to solve it would be the following:

Assuming that CellPhone is the cell that has the phone number to be checked, and MyPhoneRange is the range you want to check the phone numbers in.

Formula:

Please Login or Register  to view this content.

Now drag down this formula till the last row.
Now you have a column that shows whether that phone number is duplicated, and you can sort the whole table by this column, to see the duplicates easily.

This has helped me a little bit, I changed it a bit because I need to compensate for cell, work, and home phone numbers. when I filter though it only tells me if I have a duplicate, not the location of where it is or the ID #.... if I was only working with even a few hundred it wouldn't be that bad... but I have a file of several hundred thousand ID # that I am sorting through. is there any way to give me the location or the ID # that has the repeating phone number?

This is what the formula looks like right now:
=IF(COUNTIF($B$2:$D$11, B2)>1, "Duplicate", "")

I created a smaller test group te determine if it is working the way I need it to before I go large scale

If you filter the whole table, you should be able to retreieve the id information.
But if you want to copy this whole list of id's that have duplicate phone info, I suggest first sorting according to the duplicate column (i.e. the column containing the info whether phone is duplicate or not). Then you could select the id range and copy and paste it to wherever you want.

does this help at all
if the phone numbers are in column A
then in
B2 =IF(COUNTIF(A$2:A$10,A2)>1,ADDRESS(ROW(),1,4),"")

will show the cell reference

and this in C2 and copy down
=IFERROR(INDEX(A2:A15, MATCH(0, COUNTIF(C1:$C$1, A2:A15)+IF(COUNTIF(A2:A15, A2:A15)>1, 0, 1), 0)), "")
as an array formula - use shift+control+enter to setup the array and get {} around the formula

will pull out all the entries that are duplicated

The first part of the formula is working, but the second is just giving me a blank cell no answer. even though on my small scale i can see that the same number is listed more than once. I was reading that this might be one thing that Excel can't do and it will have to be written as a SQL query. If it gets the answer i am looking for that will help but I would have pefered to learn how to do it in Excel

has the formula in the formula bar - got {} around it - it has to be entered as an array formula
use shift+control+enter to setup the array and get {} around the formula

see attached sample