Home > Forum Home > Developing Business Administration Solutions > phone number stored in 3 columns Share

Phone number stored in 3 columns

Excel Help for Phone Number Stored In 3 Columns in Developing Business Administration Solutions

Forum TopicPost Reply Login

Phone Number Stored In 3 Columns

Rate this:
(3/5 from 1 vote)
ConfusedWe have a DO NOT CALL list - where each phone number is stored in a separate row in 3 columns. Example phone number 303-408-5566 would be stored in 1 row Column 1 303, Column 2 408, Column 3 5566. I need to be able to look up a phone number to see if it is on the DO NOT CALL list. The find feature only works on one Column and its only a combination of the 3 columns that makes a unique result.
 Posted by on
Replies - Displaying 1 to 1 of 1Order Replies By: Most Recent | Chronological | Highest Rated
OopsThe simplest method would be to create a separate column with a concatenation of the phone number columns and then use that column for the VLOOKUP function.
e.g. =TEXT(A1)&"-"&TEXT(B1)&"-"&TEXT(C1)

Another more complex method is to use MID functions to extract each part of the phone number, convert the text to values, match them to the columns, and test if the matching index is the same for all three parts. 

As an example, suppose the 3 parts of the phone numbers are in columns A,B and C from row 1 to 100 and the phone number is look for is in cell E1.  the following formula will return TRUE is the phone number is found.

=AND(MATCH(VALUE(MID(E1,1,3)),A1:A100,1)=MATCH(VALUE(MID(E1,5,3)),B1:B100,1), MATCH(VALUE(MID(E1,5,3)),B1:B100,1)=MATCH(VALUE(MID(E1,9,4)),C1:C100,1))
 Excel Business Forums Administrator
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for phone number stored in 3 columns in the