+ Reply to Thread
Results 1 to 3 of 3

Lookup Issue with Data Set

  1. 11-10-2010, 11:25 PM #1
    usc1382
    usc1382 is offline
    Registered User
    Join Date
    10-26-2010
    Location
    USA
    MS-Off Ver
    Microsoft Office 2010
    Posts
    32

    Lookup Issue with Data Set

    I am trying to take the Data set in Sheet One that is comprised of Name & Account # and then have separate sheets as in Sheet Two & Three that are based upon each Name and organize the data by account numbers. The issue I am having is my simple vlookup is producing the same account number and trying to figure out the mising step in showing all relevant account numbers per Name. The area highlighted in purple is my result while what is in blue is the result i am trying to achieve. Please see the attachment THANKS!
    Attached Files Attached Files
    Last edited by usc1382; 11-11-2010 at 06:50 PM.
    Reply With QuoteRegister To Reply
  2. 11-11-2010, 12:49 AM #2
    stanleydgromjr
    stanleydgromjr is offline
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Lookup Issue with Data Set

    usc1382,


    Detach/open workbook VlookupNth - usc1382 - EF752807 - SDG15.xls, and checkout the Function VlookupNth on Sheet2 and Sheet3.
    Attached Files Attached Files
    Last edited by stanleydgromjr; 11-11-2010 at 12:51 AM.
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
    Reply With QuoteRegister To Reply
  3. 11-11-2010, 08:09 AM #3
    outofthehat
    outofthehat is offline
    Valued Forum Contributor
    Join Date
    10-17-2010
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013, 2016, 2019, 365
    Posts
    294

    Re: Lookup Issue with Data Set

    Attached sheet uses a helper column to identify the rows containing the name you're interested in, assuming they all appear in a block. It uses MATCH in the first cell to find first instance of a given name, then checks each subsequent row to see if it's still the same. In each case it identifies how far down the column it appears and returns that value.

    The second column then uses INDEX to return the 2nd value in that row.

    With this, you could structure your book so that you choose the name at the top of 2nd sheet, and it retrieves/analyses the data for that person, rather than having a sheet for each. Depends on what you're trying to do...

    I've made a note of an optional addition to get rid of the #N/A errors.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1