Excel.Tips.Net ExcelTips (Menu Interface)

Pulling a Phone Number with a Known First and Last Name

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Pulling a Phone Number with a Known First and Last Name.

Kimm has a worksheet that has three columns in it. Column A contains the last name of a person, column B contains the first name, and column C contains the phone number for the individual. If Kimm knows the person's first and last name (say they are in cells F1 and F2, respectively), she wonders what sort of lookup formula she would use to return the phone number for the first person matching that first and last name.

There are actually several different formulas you could use to figure out the phone number. Most of the approaches involve using array formulas, which are always entered in a cell by using Ctrl+Shift+Enter. To make the formulas easier to understand, it is best to work with named ranges. For instance, set up the following names:

  • Select all the last names, in column A, and give them a name such as LNames.
  • Select all the first names, in column B, and give them a name such as FNames.
  • Select all the phone numbers, in column C, and give them a name such as Phones.

Here are a variety of array formulas you could use to find the phone number:


In addition, you can construct an array formula that relies upon the ROW function, as shown here:


If you use this approach (relying upon the ROW function), you may need to adjust the "-1" portion of the formulas to reflect the number of rows that appear before your actual data. In this instance, the row is decremented by one because the first row of the data table consists of the headers for each column; the data itself begins in row 2.

Note that all of the formulas used thus far rely upon combining the first name with the last name in order to make a comparison. This has the chance of running into "false positives" in some instances. For example, let's say that two of the names you have in your data are Thom Astonfield and Thomas Tonfield. Since the case of the letters in the names doesn't matter in these formulas, when you combine first and last names of these individuals, they are exactly the same. Thus, if you are looking for the phone number for Thomas Tonfield and his name appears in the list after Thom Astonfield, then you'll always get Thom's phone number instead of Thomas'.

To get around this potential problem, you may want to include some sort of separator between the first and last name. Using one of the formulas from earlier in the tip, all you would need to do is make an adjustment, as shown here:


The addition of the colon between the first and last names acts as a separator, eliminating the likelihood of false positives.

You should also realize that if cells F1 and F2 contain a name that doesn't appear in the data at all, the information you get back from the formula will be erroneous. Depending on the variation of the function used, you may get back an actual error condition (such as #N/A or #REF) or you may get back plain wrong data. In those instances where an error condition is returned, you may want to adjust your formula to account for the possibility of not finding a match, in this manner:

"no phone",INDEX(Phones,MATCH(F1&":"&F2,FNames&":"&LNames,0)))

There are some things that you should keep in mind when using these types of formulas. First and foremost, the reliability of the information you get back is going to depend largely on the quality of the information in your data list. If your data has misspellings in it, contains blanks, is sorted in some strange order, or there are multiple entries for the same person, it can affect what the formula returns.

If you aren't too sure about the quality of your data, you may want to simply use Excel's filtering capabilities rather than a formula. Apply an AutoFilter, and you can use the first two columns of your data to pick first and last name. This will then return any phone numbers for the individual you select. It is very simple to do and makes selecting the data you need easy.

Finally, you should realize that there are other approaches you can use to addressing the problem. For instance, you could create a formula that uses the DGET function, but doing so would require the addition of a small criteria table to your worksheet or workbook. Since Kimm specified that she couldn't add intermediate results in a worksheet, then an editorial decision was made to not include the DGET function as a solution since it would require the addition of the criteria table.

In addition, if you are comfortable with using macros, you could also create a user-defined function that would examine the data and return the requested phone number. The benefit to using such an approach is that it provides you greater flexibility in processing the information that the function actually returns.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10478) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Pulling a Phone Number with a Known First and Last Name.

Related Tips:

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 5+3 (To prevent automated submissions and spam.)
           Commenting Terms

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.