+ 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
    53

    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
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    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

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

    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
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    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