+ Reply to Thread
Results 1 to 15 of 15

Searching for a value and returning corresponding row

  1. #1
    Registered User
    Join Date
    07-18-2018
    Location
    Melbourne
    MS-Off Ver
    2013
    Posts
    7

    Searching for a value and returning corresponding row

    Hi

    From the set of data below:

    Name Dept Age
    Henry 501 28
    Stan 201 19
    Mary 101 22
    Larry 301 29
    Lynn 201 22
    Harry 401 21
    Joe 101 23
    Lynn 301 30

    I am looking to search for "Lynn" in the first column and return the entire row.

    The ideal outcome would be:

    Name Dept Age
    Lynn 201 22
    Lynn 301 30

    On a separate worksheet

    Any help would be appreciated

    thanks

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Searching for a value and returning corresponding row

    Attach your sample workbook.

    Go Advanced -> Manage Attachments -> Upload

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,437

    Re: Searching for a value and returning corresponding row

    Welcome to the forum!

    Is your sample data truly representative? Is there no other identifier than the person's first name? No staff ID code or anything like that?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,692

    Re: Searching for a value and returning corresponding row

    Going with what is posted in #1.

    This formula in Sheet2 A2 filled across column C and down until you get blanks.
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    A
    B
    C
    D
    E
    1
    Name
    Dept
    Age
    Lynn
    2
    Lynn
    201
    22
    3
    Lynn
    301
    30
    Dave

  5. #5
    Registered User
    Join Date
    07-18-2018
    Location
    Melbourne
    MS-Off Ver
    2013
    Posts
    7

    Re: Searching for a value and returning corresponding row

    Hi

    The Data is just sample data - just made it for the purposes of my Query.

    Attached

    thanks
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-18-2018
    Location
    Melbourne
    MS-Off Ver
    2013
    Posts
    7

    Re: Searching for a value and returning corresponding row

    Hi

    It would be based on the persons name

    Thanks

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,437

    Re: Searching for a value and returning corresponding row

    See post #4.

    What do you do when you have two Lynns, for example, in the same department?

  8. #8
    Registered User
    Join Date
    07-18-2018
    Location
    Melbourne
    MS-Off Ver
    2013
    Posts
    7

    Re: Searching for a value and returning corresponding row

    Hi

    Lynn Would only come up once. There would be no other lynns if that makes sense. So it would be for example Lynn Smith, there would be no other Lynns, Lynn Johnston, Lynn Green Etc

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,437

    Re: Searching for a value and returning corresponding row

    That's very fortunate. Just bear in mind that if you do recruit another Lynn who will be working in the same department, then what you are building now will not be future-prooof. You would be FAR BETTER OFF using first names AND surnames, and better still, a unique staff identifier. But you know best, of course ...

  10. #10
    Registered User
    Join Date
    07-18-2018
    Location
    Melbourne
    MS-Off Ver
    2013
    Posts
    7

    Re: Searching for a value and returning corresponding row

    Hi

    Copied the formula in - and wouldn't bring back any data.

    Have attached the sample data

    Thanks again

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,437

    Re: Searching for a value and returning corresponding row

    No new sample attached.

  12. #12
    Registered User
    Join Date
    07-18-2018
    Location
    Melbourne
    MS-Off Ver
    2013
    Posts
    7
    Quote Originally Posted by AliGW View Post
    That's very fortunate. Just bear in mind that if you do recruit another Lynn who will be working in the same department, then what you are building now will not be future-prooof. You would be FAR BETTER OFF using first names AND surnames, and better still, a unique staff identifier. But you know best, of course ...
    Hi the actual data sheet will have full names anyway- i am just looking for a formula that will adjust to that

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,437

    Re: Searching for a value and returning corresponding row

    OK, well if you are happy changing the formula after the event, fine. If you were to include (dummy) surnames here, we could provide a solution that works out of the box. Matching on two columns requires a different approach to matching on one.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,437

    Re: Searching for a value and returning corresponding row

    Your sample data is still the same, by the way:

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    1
    Name Dept Age
    2
    Henry
    501
    28
    3
    Stan
    201
    19
    4
    Mary
    101
    22
    5
    Larry
    301
    29
    6
    Lynn
    201
    22
    7
    Harry
    401
    21
    8
    Joe
    101
    23
    9
    Lynn
    301
    30
    Sheet: Data Sheet

  15. #15
    Registered User
    Join Date
    07-18-2018
    Location
    Melbourne
    MS-Off Ver
    2013
    Posts
    7

    Re: Searching for a value and returning corresponding row

    Sorry is that attached now?
    Attached Files Attached Files
    Last edited by melbournewarriors; 07-18-2018 at 10:05 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. searching different sheets and returning in the first one...
    By fransis1976 in forum Excel General
    Replies: 2
    Last Post: 03-31-2014, 11:24 AM
  2. Searching & returning
    By acoger in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-08-2012, 05:30 AM
  3. [SOLVED] Searching and returning row number of a value
    By Robert_Steel@nothanks.com in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 02:05 PM
  4. [SOLVED] Searching and returning row number of a value
    By MikeDH in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  5. Searching and returning row number of a value
    By MikeDH in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 11:05 AM
  6. [SOLVED] Searching and returning row number of a value
    By MikeDH in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  7. [SOLVED] Searching and returning row number of a value
    By MikeDH in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM

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