I have an Excel file with two sheets and I'm trying to display information from the Master Sheet onto Sheet 2:
Master Sheet
| ITEM | CRITERIA 1 | CRITERIA 2 |
| 001 | ABC | 123 |
| 002 | DEF | 456 |
| 003 | ABC | 789 |
| 004 | ABC | 123 |
Right now I'm using the following formula to display all items from the Master Sheet that have a Criteria 1 of "ABC" (where "masterlist" is the name of the table containing the information):
=INDEX(masterlist[ITEM], SMALL(IF("ABC"=masterlist[CRITERIA 1], ROW(masterlist[ITEM])-1,""), ROW()-2))
| ITEM | CRITERIA 1 | CRITERIA 2 |
| 001 | ABC | 123 |
| 003 | ABC | 789 |
| 004 | ABC | 123 |
But I want to display all items that match both Criteria 1 of "ABC" and a Criteria 2 of "123".
I've been able to display and repeat only the first entry that matches both of those criteria using:
=INDEX(masterlist[ITEM],MATCH("ABC",masterlist[CRITERIA 1],0),MATCH("123",masterlist[CRITERIA 2],0))
| ITEM | CRITERIA 1 | CRITERIA 2 |
| 001 | ABC | 123 |
| 001 | ABC | 123 |
| 001 | ABC | 123 |
| 001 | ABC | 123 |
But I can't seem to loop through the data like I can in the first formula, no matter what I try. I've tried combining the two formulas every way I can think of.
Is there an easier way to do this? I need help!
