+ Reply to Thread
Results 1 to 4 of 4

Extract data list from Table

  1. #1
    Registered User
    Join Date
    09-04-2012
    Location
    Texas
    MS-Off Ver
    Excel 2016, 2019
    Posts
    47

    Extract data list from Table

    I have a table (ContactList) with my contact (Tab1). In cell B2 I can manually enter or select from the dropdown list. This return the individuals information in cells B3-B9. On the Dept Contact List worksheet, in cell B2, I would like to be able to select a Dept and have all the member of that dept to be listed with the desired info. I have attempted to INDEX MATCH but can only capture just one record and I suspect that the solution doesn’t need to be complicated.

    Any help is greatly appreciated

    Jaycee66
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    725

    Re: Extract data list from Table

    In Cell A4 try this Array Formula:
    =IFERROR(INDEX(ContactList[LNAME],SMALL(IF(ContactList[DEPT]='Dept Contact List'!$B$1,ROW(ContactList[DEPT])-ROW(INDEX(ContactList[DEPT],1,1))+1),ROW()-3)),"")
    (To enter Array Formulas, while in the formula bar, hold the Ctrl and Shift key while hitting Enter. You should see brackets {} around the formula).
    Then drag the formula down for as far as you need (you can drag it farther than necessary as it will just show blanks if there's no data).
    You would then continue to all the other values.
    Does this help?
    Attached Files Attached Files
    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  3. #3
    Registered User
    Join Date
    09-04-2012
    Location
    Texas
    MS-Off Ver
    Excel 2016, 2019
    Posts
    47

    Re: Extract data list from Table

    Gregb11,

    Your solution was what I was looking for. I was working a formula very similar, the ",ROW()-3)),"")" what the area that I couldn't figure out. Thanks again for the assist.

    Jaycee66

  4. #4
    Valued Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    725

    Re: Extract data list from Table

    You're welcome. Glad it worked out.

+ 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. Replies: 1
    Last Post: 04-15-2020, 12:48 PM
  2. [SOLVED] Extract data from a table to a list
    By XLalbania in forum Excel General
    Replies: 5
    Last Post: 10-10-2018, 06:13 PM
  3. [SOLVED] Extract data from specific table using dropdown list to select table
    By Maggie.S in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-08-2018, 09:21 AM
  4. [SOLVED] Extract Multiple Data as list from a Table
    By Gokhan Bozkurt in forum Excel General
    Replies: 6
    Last Post: 03-30-2016, 11:28 AM
  5. [SOLVED] Extract unique values from a table and list along a row
    By doctorblyth in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-05-2013, 04:33 PM
  6. Replies: 0
    Last Post: 07-10-2013, 08:29 AM
  7. How to extract rows from a table list
    By simke in forum Excel General
    Replies: 0
    Last Post: 12-18-2011, 10:13 AM

Tags for this Thread

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