+ Reply to Thread
Results 1 to 4 of 4

Array formula + Array formula with criteria that lookups a Table

  1. #1
    Forum Contributor
    Join Date
    11-04-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    334

    Array formula + Array formula with criteria that lookups a Table

    Sheet "DTL" contains a Table ("Details") in Columns A:X

    On another sheet I have a list of names in Column B which I need to lookup in the "Details" table and return multiple values. "Name" = column E in the table.
    I need to find the first 16 values that relate to "name".

    Then I need to find the first 10 values of name ONLY if "Major" = "A" (Column X in the table).

    I am assuming an array formula will be what I need, however, I don't know enough about array formulas to know where to start!

    I have attached a sample workbook with a before and after and the table to look for values.

    In my actual workbook there will be many more than 16 values for each name and many more than 10 values for each "name" + "Major = A".
    I am trying to return the 16 and 10 most recent values based on the date. The table will be sorted in date order (newest to oldest).

    Any assistance appreciated.
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,849

    Re: Array formula + Array formula with criteria that lookups a Table

    Hi,

    Instead of an Array answer, this looks like a simple Advanced Filter problem to me where the Major is "A" and you sort the result by date and take the top rows. Have you considered an Advanced Filter solution?
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Contributor
    Join Date
    11-04-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    334

    Re: Array formula + Array formula with criteria that lookups a Table

    Hi Marvin,

    Thanks for the reply.

    I actually need both. i.e. the most recent 16 values where the major could be anything (from A-L) AND the most recent 10 values where the major is "A".
    I need to return the values like this because most of the time the most recent 16 values does not always include any or all of the values where major = "A".

    For example:

    John Smith might have 58 values in the table and the most recent 16 may not include a value where major = "A" or the most recent 16 might only include 3 values where major = "A".
    For my purposes I need BOTH...the most recent 16 in date order AND the most recent 10 where Major = "A" in date order.

  4. #4
    Forum Contributor
    Join Date
    11-04-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    334

    Re: Array formula + Array formula with criteria that lookups a Table

    If I want to filter my table will I need to add each name as a filter criteria or can I point the criteria to a range of cells on the worksheet?

    So instead of having:
    Please Login or Register  to view this content.

    Can I use something like:
    Please Login or Register  to view this content.

+ 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. Array formula to return a table excluding rows meeting a certain criteria
    By puckman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-12-2013, 07:54 AM
  2. [SOLVED] Array Formula to Lookup and Return All Rows in Table that Meet Single Criteria
    By Torkel74 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-15-2013, 10:41 PM
  3. Replies: 13
    Last Post: 02-16-2012, 06:45 AM
  4. non-array formula with multiple lookups
    By ashleykelley in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-14-2007, 09:57 AM
  5. Replies: 0
    Last Post: 07-27-2005, 11: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