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!
Maybe consider the use of FILTER():
=FILTER(DATA!A:AF, B1&C1&E1=DATA!AA:AA&DATA!AD:AD&DATA!AC:AC)
TRANSPOSE to "switch" it from a column into a row) was exactly what I needed, thank you!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.
This formula
=FILTER(A1:C3,B1:B3="ADD")
On this table
| Alpha | Beta | Gamma |
|---|---|---|
| MP | foo | 10 |
| PZ | ADD | 2 |
| CP | to | 5 |
Get this result
So great answer by @JPV to solve INDEX MATCH with FILTER formula.
Then related to the comment about TRANSPOSE makes rows and columns will be swapped, which it wasn’t required in my case
