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

Pulling a Phone Number with a Known First and Last Name

by Allen Wyatt
(last updated July 26, 2014)

8

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:

=SUMPRODUCT(--(LNames=F2),--(FNames=F1),Phones)
=INDEX(Phones,MATCH(F1&F2,FNames&LNames,))
=INDEX(Phones,INDEX(MATCH(F1&F2,FNames&LNames,0),))
=OFFSET(C1,MATCH(F1&F2,FNames&LNames,),)

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

=INDEX(Phones,SUMPRODUCT((F1&F2=FNames&LNames)*(ROW(FNames)-1)))
=INDEX(Phones,MIN(IF((FNames=F1)*(LNames=F2),(ROW(Phones)-1))))

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:

=INDEX(Phones,SUMPRODUCT((F1&":"&F2=FNames&":"&LNames)*(ROW(FNames)-1)))

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:

=IF(ISERROR(INDEX(Phones,MATCH(F1&":"&F2,FNames&":"&LNames,0))),
"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 (10479) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Pulling a Phone Number with a Known First and Last Name.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Marking Multiple Documents

After using Word for a while, it is easy to accumulate quite a few documents. At some point you may want to make a change to ...

Discover More

Moving and Copying Graphics Objects

Excel doesn't just work with numbers and text. You can also add graphics objects to your worksheets, and then use Excel's ...

Discover More

Copying and Moving Footnotes

Want to get your footnotes from one place to another in a document, or even from one document to another document? It's easy ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Counting Consecutive Negative Numbers

If you have a range of values that can be either positive or negative, you might wonder how to determine the largest ...

Discover More

Dealing with Circular References

Circular references occur when a formula includes a reference to the cell in which the formula appears. Here's how you can ...

Discover More

Returning the Minimum of Integers of a Range

If you have a range of numbers that contain both integers and decimal numbers, you may have a need to determine the minimum ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 8 - 2?

2017-08-20 02:53:41

Michael (Micky) Avidan

@Paolo Gera,
The "Two dashes" are "Two Minus signs).
The result of the formulas first two segments are: TRUE / FALSE with which SUMPRODUCT is not capable to handle.
The sirst minus turns TRUE to: -1 and the second returns it into: 1
(In the samw way FALSE becomes 0).
There are 3 workarounds:
a) =SUMPRODUCT(N(LNAMES=F2),N(FNAMES=F1),PHONES)
or:
b) =SUMPRODUCT((LNAMES=F2)*(FNAMES=F1)*PHONES)
or:
c) =SUMIFS(PHONES,LNAMES,F2,FNAMES,F1)
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” Excel MVP – Excel (2009-2018)
ISRAEL


2017-08-19 19:13:50

Paolo Gera

What are the two dashes for in this formula?
=SUMPRODUCT(--(LNames=F2),--(FNames=F1),Phones)
Thanks in advance...


2014-09-02 10:42:49

lacey fluharty

Find my iphone tracker


2014-07-29 08:47:28

Glenn Case

Thanks, Bryan


2014-07-29 07:06:34

Bryan

Glenn: personally I have no problem with array formulas. I have seen some people who try to avoid them because they are afraid someone will mess with their sheet and, not recognizing an array formula, won't be able to get the formula to work again. I can kind of see what they mean, because you have to use a different keystroke to use the formulas, but on the other hand if you are worried about people messing with your sheet, you need to stop them from messing no matter what kind of formula you use.

As to the volatile function, there are a number of functions (OFFSET, INDIRECT, RAND, RANDBETWEEN, TODAY, NOW, maybe a few others) that are always considered "dirty" and recalculate at every recalc cycle (which, on automatic calculation, is any time you change ANY cell, even if it's not related to the cell containing the volatile function).

In other words, if you put "=A1" into cell A2, and "=INDIRECT("A1")" into cell A3, cell A2 will only recalculate when you change cell A1, whereas cell A3 will recalculate when you change any cell, even though, obviously, you only need it to recalculate when cell A1 changes.

It's not a HUGE deal, especially if you don't have a lot of them around, but it's good practice to avoid them if not absolutely necessary.


2014-07-28 15:07:27

Glenn Case

Sorry, I menat Bryan...


2014-07-28 15:05:23

Glenn Case

Barry:

1) Why avoid array formulae? What's the downside?
2) What do you mean by formula 4 being volatile?


2014-07-28 08:51:01

Bryan

* Formula 1 does not need to be array entered and will not work if the phone numbers are entered as text

* Formula 2 is probably the best, unless you are afraid to put array formulas in your worksheet

* Formula 3 does not need to be an array formula, so it is the best if you are avoiding them; otherwise it's redundant as Formula 2 is shorter and simpler (though I have to admit it's a clever trick to make it a non-array formula, and I'm not 100% sure how/why it works)

* Formula 4 should be avoided because it is volatile

* Formula 5 does not need to be array entered and is needlessly complicated and has hard-coded values that won't update if the cells are moved around

* Formula 6 suffers from the same problems as Formula 5, but it's also array-entered

* The IF/ISERROR construct was replaced by the IFERROR function 7 years ago

Therefore, I submit that the only formulas that anyone need be concerned about 2 (Array entered) or 3 (standard formula), or their "error-proof" counterparts:

=IFERROR(INDEX(Phones,MATCH(F1&":"&F2,FNames&":"&LNames,)),"Not Found") (Array-entered)

=IFERROR(INDEX(Phones,INDEX(MATCH(F1&":"&F2,FNames&":"&LNames,0),)),"Not Found") (Standard formula)


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.