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.

It works!!! You are a genius!

Thank-you thank-you thank-you!!

Originally Posted by VBA Noob

Try

=IF(ISERROR(INDEX(AllSites!$B$1:$B$43,SMALL(IF((AllSites!$C$1:$C$43=Results!$J$2)*(AllSites!$G$1:$G$43=Results!$K$2),ROW($1:$43)),ROW(1:1)),COLUMNS($A:A))),"",(INDEX(AllSites!$B$1:$B$43,SMALL(IF((AllSites!$C$1:$C$43=Results!$J$2)*(AllSites!$G$1:$G$43=Results!$K$2),ROW($1:$43)),ROW(1:1)),COLUMNS($A:A))))

VBA Noob

Does anyone know how to revise this formula so that I can return the entire row of data (i.e., rather than just the specific cell in the first column)?

I've tried modifying the formula so that it sets the column value in the index function to zero, but so far I must be doing something wrong...

bump bump ...

Hi

A different formula, also array entered, which brings back a different set of results.

If you want other columns of data, then copy the formula across, but change the source column being indexed.

Please Login or Register  to view this content.

This is currently bringing back data from allsites!B. If you want column C, then change it to allsites!$b$1:$c$43 etc.

rylo

Thanks Rylo! Interesting approach. However, I wasn't able to get it to work in my spreadsheet. Can you attach the spreadsheet you tested it in so I can play with it a little more?

Hi

Heres a file. It is based on the second sample file you put on this site. I've just put my formulas into column E.

rylo