Excel 2010 Lookup Function

Posted on 2016-08-01
Last Modified: 2016-08-01
I uploaded a sample Excel file with 2 tabs.   The first first tab, named Lookup contains in Cell A2 a Phone Number.  I would like to type in a lookup formula in Cell B2 to scan the first 3 columns in the Phone Tab worksheet at the same time and bring back a match number.  The full file has many more rows of phone number,  I am not sure the easiest method to do this, also is there a way to indicate which Column where a match is found as well (Cell, Home or Work).  Thanks for any help.
Question by:fjkaykr11
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 52

Expert Comment

by:Ryan Chong
ID: 41738412
do you mean this formula in tab: Lookup cell B2?
=IF(A2="", "", IF(ISERROR( VLOOKUP(A2,Phone!A:A,1,FALSE)), IF(ISERROR( VLOOKUP(A2,Phone!B:B,1,FALSE)),IF(ISERROR( VLOOKUP(A2,Phone!C:C,1,FALSE)), "Business", "Home"), "Home"), "Cell"))

Open in new window

LVL 47

Accepted Solution

Wayne Taylor (webtubbs) earned 250 total points
ID: 41738453
This formula will also work...

=INDEX(Phone!A$1:C$1, 1, SUMPRODUCT((Phone!A$1:C$100=A2)*COLUMN(Phone!A$1:C$1)))
LVL 52

Assisted Solution

by:Ryan Chong
Ryan Chong earned 125 total points
ID: 41738456
opps, correction, for my suggestion, it should be as:
=IF(A2="", "", IF(ISERROR( VLOOKUP(A2,Phone!A:A,1,FALSE)), IF(ISERROR( VLOOKUP(A2,Phone!B:B,1,FALSE)),IF(ISERROR( VLOOKUP(A2,Phone!C:C,1,FALSE)), "Not Found", "Business"), "Home"), "Cell"))

Open in new window

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 31

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 125 total points
ID: 41738461
Since your Phone Sheet contains the phone numbers only so there is no point of returning the same phone number on Lookup Sheet which you are entering in A2. I think you would only like to know if the phone number entered in A2 is found on Phone sheet and in which column i.e. whether that phone number belongs to Cell, Home or Business and if it is not found, the formula should either return "Not Found" or a blank to let you know that the phone number you entered in A2 is not available on Phone sheet.
If my assumption is correct, you may try the following formula...

On Lookup Sheet
In B2
=IF(ISNUMBER(MATCH(A2,Phone!A:A,0)),"Cell",IF(ISNUMBER(MATCH(A2,Phone!B:B,0)),"Home",IF(ISNUMBER(MATCH(A2,Phone!C:C,0)),"Business","Not Found")))

Open in new window


Author Closing Comment

ID: 41738493
These all work, thanks so much for the quick assistance.   What I am looking to do is a bit more complex,  so returning the phone number would be helpful along with the matching phone type (cell, home or work), . But this should get me started.  Thanks again.
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41738496
You're welcome!

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question