Excell 2013 - Vlookup of phones number issue

DGWhittaker
DGWhittaker used Ask the Experts™
on
I am trying to perform a v-lookup on a phone number.
The table I am using to define the criteria is formatted as (XXX) XXX-XXXX
The table I am using to pull the data from is formatted as XXXXXXXXXX

I changed the format to a phone number in the same format to no avail.

Any suggestions on how I can normalize this data so the vlookup will perform as needed?

I have attached the file for review

Thanks!
Dennis
December-2015-Manifests-151217.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
Hi,

pls try

=VLOOKUP(--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"(",""),")","")," ",""),"-",""),Sheet1!N:Z,13,0)


Regards
Top Expert 2016

Commented:
try

=IFERROR(VLOOKUP(--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D3,"(",""),")","")," ",""),"-",""),Sheet1!N:Z,13,0),"")
Microsoft Excel Expert
Top Expert 2014
Commented:
put this in M2

checks the day phone if it fails then checks the eve phone and then if not found then returns Not found

=IFERROR(IFERROR(VLOOKUP(--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([@[Day Phone]],"(",""),")",""),"-","")," ",""),Sheet1!N3:Z14143,13,FALSE),VLOOKUP(--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([@[Eve Phone]],"(",""),")",""),"-","")," ",""),Sheet1!O3:Z14143,12,FALSE)),"NOT FOUND")

Open in new window

Author

Commented:
Perfect!
Thanks Jim!
Dennis
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
you are welcome. i am glad i was able to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial