Can Index or Match return multiple results?

I've spent the last week trying to do the following, with little to no success....and I'm running out of hair to pull out!!

I have a worksheet (let's call it DATA) with the following column headers:
* Site Name
* Year Initiated
* Status

plus a whole bunch of other columns of information.

On a separate worksheet (let's call it RESULTS) I'm trying to create a summary of all the Site Names where the Year Initiated = "2004" and the Status = "Active". There will be multiple occurences where this is true.

I then want to create further results where the year = 2005, 2006, etc., etc. I've been trying to use the INDEX function with variations of MATCH, SMALL, ROW, etc., etc., but so far, no luck.

Can anyone help? Please??

Thanks in advance!

Note, Advanced Filtering is not an option as I need to do further manipulating of the results on the RESULTS worksheet.

VBA, this looks great! I'm heading to a conference for the next 2 days but will look at this more closely and see if I can replicate the function with the live dataset. I haven't used ISERROR before so need some time to understand how it works.

Thanks for taking the time and effort to put together the example and functions! Much appreciated!!

Originally Posted by VBA Noob

Hi,

Does this example help

VBA Noob

Hi VBA Noob,

I've been trying to implement your solution into my spreadsheet, but only with limited success (the overarching IF statement returns the 'value_if_true').

Can you look at the attached and see if you can easily find the problem? I'm trying to display the results where the year and status are '2005 / 06' and 'PS'.

Thanks!

Hi VBA,

It's working better, but not pulling the correct data out. Instead it appears to be grabbing every second site name and then starts to ignore the requirement for the site status to equal "PS". Note if I'm taking too much of your time, feel free not to respond and I'll continue trying to figure it out. I've attached the updated spreadsheet with your revisions and the results. See attached.

I also better understand your first ROW function, but the second one and the COLUMNS function are still confusing for me.