Using INDEX MATCH to return multiple rows in Google Sheets

Question

I'm currently trying to use an INDEX/MATCH formula to return values from a data sheet into a summary sheet based on user entered criteria.

This is the formula I have so far:

=INDEX(DATA!A:AF,MATCH(B1&C1&E1,DATA!AA:AA&DATA!AD:AD&DATA!AC:AC,0))

However it only returns the first row that matches the results. How can I get it to return all of the rows that match the results of the MATCH?

Thanks!

Answer 1

Maybe consider the use of FILTER():

=FILTER(DATA!A:AF, B1&C1&E1=DATA!AA:AA&DATA!AD:AD&DATA!AC:AC)

Answer 2

For anyone else perusing this topic several years later, you can also use the query formula. This is a useful article to better help learn it: https://www.benlcollins.com/spreadsheets/google-sheets-query-sql/

In this case, you can you use:

=Query(DATA!A:AF,"select * where AA = "&B1&" AND AD = "&C1&" AC = "&E1)`

The Query formula uses a version of the SQL to help you filter and return the exact data you need. I can't stress how useful it can be!

The string break and ampersands are necessary to reference a field outside of the data range notated in the first field of the Query formula.