Thread: Return array based on multiple criteria

Hi all,

I'm hoping someone can point me in the right direction on this, I feel as though the answer should be obvious...

I have a worksheet with data in multiple columns (flat-file database structure, each column is a field and each row is a record):

Name | Location | Date | Data1 | Data2 | Data3

(I'm using | to indicate a column break in this example.)

For example:

domain.com | All Canada | Nov-10 | 11123 | 22123 | 33123
example.com | All Canada | Nov-10 | 10321 | 57321 | 29819
another.com | All Canada | Nov-10 | 47123 | 81723 | 19283
onemore.com | Ontario | Nov-10 | 12823 | 123945 | 12362
again.com | Ontario | Nov-10 | 72839 | 81937 | 9135
lastone.com | Ontario | Nov-10 | 92834 | 93241 | 56321

All columns are unsorted. The Name and Date columns have values that repeat. The Location column has two possible values, 'All Canada' or 'Ontario'. There are no blank cells - each row has values in every column.

The 'unique key' is a combination of Name, Location & Date columns: there is only one row in the worksheet containing 'domain.com | All Canada | Nov-10'. (In this example, there may also be a row for 'domain.com | Ontario | Nov-10', which is also a unique record/row on the worksheet.)

I have data going back a couple of years and the number of rows in the worksheet grows each month as I add in the data set (records) for that month. Also, the number of 'names' in the set (number of records/rows) for each month varies month to month, for example: 10 rows of data for Oct-10, 12 rows for Nov-10, 13 rows for Dec-10 etc.

I've set up dynamic named ranges for the data (one for each column, which automatically includes as many rows as have data in that column): Date Location Media Data1 Data2 Data3.

What I'm trying to do is return an array of all the Names available for a given month and location. For example, if 'All Canada' and 'Nov-10' are the criteria, the returned array should be {domain.com, example.com, another.com}. If 'Ontario' and 'Nov-10' are the criteria, the returned array should be {onemore.com, again.com, lastone.com}.

I'm already using SUMPRODUCT in the workbook to return specific data values from the data worksheet based on multiple criteria:

=SUMPRODUCT(--(Media="domain.com"),--(Location="All Canada"),--(Date="Nov-10"),--(Data3))

This example returns 33123.

Great for returning a single value, however I need to return an array of values. I just can't figure out how to build an array including all the records available for a month and a location. I don't want to use VBA and I intend the formula to be a named range itself: AvailNames={array returned by formula}.

I've tried a few things like:

=SUMPRODUCT(--(Location="All Canada"),--(Date="Nov-10"),--(Media))

but that throws a #VALUE error.

I hope someone can point me in the right direction. I'm stumped!

Thanks,
Keith