+ Reply to Thread
Results 1 to 6 of 6

Finding a number in a multi-column array and returning the value from the leftmost column

  1. #1
    Registered User
    Join Date
    07-31-2012
    Location
    Corning, NY
    MS-Off Ver
    Excel 2007
    Posts
    3

    Question Finding a number in a multi-column array and returning the value from the leftmost column

    I need to be able to locate a telephone number within a file and have it return the name of the owner. I have a column of phone numbers in one file, and a phone list in another file. I've attached a file of a mock phone list, and hand-entered a phone number, though in reality each of the phone numbers in the separate file need an owner.

    I am trying find a telephone number (in the attached file, cell F3) from within the array A1:D5, and have the formula return the first and last name of the person with that phone number. I do not care what type of phone it is, some of the owners only have 1 phone (meaning blank cells), and the format of phone numbers is not always the same.

    Can this be done with a formula? Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Finding a number in a multi-column array and returning the value from the leftmost col

    Hi Carl,

    Welcome to the forum.

    Try using below array formula:-

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    See attached:- test(12).xlsx

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    07-31-2012
    Location
    Corning, NY
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Finding a number in a multi-column array and returning the value from the leftmost col

    Hi DILIPandey,
    Thanks for helping. Sorry it took so long for me to get back into this...been traveling all over the country for work and just now getting back to this. In trying your solution, I get the "#VALUE!" error. It appears the "SEARCH" part of the nest is responsible as I just broke that part out into a separate cell to test. It looks like the "SEARCH" function can only look within 1 cell to find the match, not the entire range of phone number cells. Seems to me that would be an impossible barrier to overcome with this type of nested string?
    Carl

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Finding a number in a multi-column array and returning the value from the leftmost col

    Hi Carl K,

    Are you entering the formula using ctrl shift enter key combination ?

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  5. #5
    Registered User
    Join Date
    07-31-2012
    Location
    Corning, NY
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Finding a number in a multi-column array and returning the value from the leftmost col

    That was it. Thanks!!! I had never worked with the array function before...looks like it may be pretty handy. Last night as I was playing around with your solution, I found a clumsier way to do the same thing without using the array...learned a bit too. Thanks for your help!
    Carl

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Finding a number in a multi-column array and returning the value from the leftmost col

    You are welcome Carl...

    Suggest you to mark this thread as [SOLVED].. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1