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
LVL 49

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 49

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

LVL 28

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 28

Expert Comment

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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now