# Array formula + Array formula with criteria that lookups a Table

1. ## 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.  Register To Reply

2. ## 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?  Register To Reply

3. ## Re: Array formula + Array formula with criteria that lookups a Table

Hi Marvin,

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.  Register To Reply

4. ## 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? ``Please Login or Register  to view this content.``

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