Solved

# Excel 2010 Lookup Function

Posted on 2016-08-01
80 Views
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.
file.xlsx
0
Question by:fjkaykr11
[X]
###### 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

LVL 52

Expert Comment

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"))
``````
file_b.xlsx
0

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)))
0

LVL 52

Assisted Solution

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"))
``````
0

LVL 31

Assisted Solution

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")))
``````
file.xlsx
0

LVL 3

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.
0

LVL 31

Expert Comment

ID: 41738496
You're welcome!
0

## Featured Post

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.
###### Suggested Courses
Course of the Month2 days, 22 hours left to enroll