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 51

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 51

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

Technology Partners: 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 30

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 30

Expert Comment

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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

749 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