Excel 2010 Lookup Function

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.
Who is Participating?
Wayne Taylor (webtubbs)Connect With a Mentor Commented:
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)))
Ryan ChongCommented:
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

Ryan ChongConnect With a Mentor Commented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
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

fjkaykr11Author Commented:
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.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.