VLOOKUP Returns Zeros When Data is Plainly There

Please see attached Zip file.

I use a robot to find telephone numbers from publicly available sources for lists of real property owners. The search criteria for each property record include Firstname, Lastname and Zip Code.

The robot returns "FirstName, Lastname","Street Address","City-State-Zip","Phone" in a CSV file which can be opened in Excel to yield four columns with those headings.

The robot is programmed to return both exact matches and approximate matches as the phone records don't always exactly match the owner's contact information found in the property records. So, the robot often returns far more rows of possible matches than there are rows of containing property records.

The task is then to use VLOOKUP to find the right phone number. Following advice received from a previous post I use the following function to produce a lookup value for VLOOKUP both in the robot data and in the original property contact information. The "concatenate" is formed by using a formula like =LastName&" "&StreetNumber&" "Zip. Then VLOOKUP is used to find the phone number.

Fortunately, VLOOKUP does work to find a phone number...for some lookup values...but not for others. VLOOKUP returns a phone number perfectly when the lookup value is found in the range PROVIDED THE MATCHING LOOKUP VALUE IS NOT FOUND IN THE PORTION OF THE RANGE THAT EXCEEDS THE COLUMN LENGTH OF THE PROPERTY RECORDS THEMSELVES. If the matching value is found in a portion of the range beyond the range of the property values it returns a zero vs. the phone number.

Being able to lookup these phone numbers from lists of robotically returned exact and inexact matches is extremely important to our work. So, any assistance with this will be very much appreciated.

Please see the attached spreadsheet for further information.

Oh, happy Father's Day to all Dads out there (like me!)

LongFisher

Taking the information in the above post as a jumping off point, I have another question that I might as well ask now.

Often a property owner will have more than one phone line (and number) to a single property. It might be a FAX, a voice line, a data line, a business line or a secondary voice line used by children or adolescents in the household. We uncover those additional lines (numbers) in our robotic searches, they appear in the data range returned by the robot and we would like to associate those additional phone numbers with the property record as well as the first phone found. I have found as many as seven additional lines to a single property even though it's a single family dwelling according to the county appraisal records.

I have absolutely no idea how one might extract all matching phone numbers from the robot data range using VLOOKUP or any combination of functions. And, I'd appreciate any advice anyone might provide.

Again, Happy Father's Day.

LongFisher

Thank you for being kind.

What a dope I was.

LongFisher

about the second part of the question (see post immediately below the one that began this thread).

LongFisher

Used INDEX and MATCH to configure an IF statement which works just fine.

Thanks for the help, pointing out my non-thinking error. I hope I was not too much of a dope.

LongFisher