Match multiple criteria and return multiple values.

Hello everyone. I’ve been looking through the message board for a few days now trying to piece together a formula that will do what I state below, but I haven’t been able to get anything I’ve found to work exactly right.

I need a formula that will:
1. Match three separate criteria from two separate workbooks,
2. Return the values on separate rows for each set of criteria matched,
3. and return different subsequent values found even if the criteria matched is the same.

I need to match the values in:
1. Book1-ColumnA = Book2 ColumnA
2. Book1-ColumnC = Book2 ColumnB
3. Book1-ColumnE = Book2 ColumnC

And return the value from:
1. Book2-ColumnD to Book1-ColumnH

These are a couple of the formulas I’ve tried:
1.=INDEX([Book2.xlsx]Sheet2!$D$2:$D$7,SMALL(IF(COUNTIF($A$2:$A$7,[Book2.xlsx]Sheet2!$A$2:$A$7),MATCH(ROW([Book2.xlsx]Sheet2!$A$2:$A$7),ROW([Book2.xlsx]Sheet2!$A$2:$A$7)),""),ROWS($A$1:A1)))

2.=INDEX([Book2.xlsx]Sheet2!$A$1:$D$7,MATCH(1,([Book2.xlsx]Sheet2!A:A=A2)*(LEFT([Book2.xlsx]Sheet2!B:B,4)=C2)*([Book2.xlsx]Sheet2!C:C=E2),0),4)

In the attached workbook, and the screenshots below, the first formula is returning the correct results, see H2 and H3, but it’s only matching on a single criteria in column A and I’m not even sure that part is working correctly because changing the value in column A doesn’t cause the formula to error out. And the second formula in column I is the one that is working the best by matching on all three criteria, but it only returns the first value it finds, see I2 and I3.

Other information that might be important:
1. I’m going to be inserting this formula using a macro and making it copy down.
2. And I’m going to have it look for and correct errors and then loop back through to recalculate.

Hi guys,

Thank you for the quick replies. This thing has been driving me nuts.

Richard, I'm looking for the results that are in column H (Test 1) on the first sheet/screenshot; they match column D from the second sheet/screenshot. Originally I had tried using a helper column, but ran into issues trying to get it to return multiple values for same matched criteria. The yellow highlighted cells in Rows 2 and 3 on both spreads match each other exactly, but I need get the both of the different ID number results from column D.

Dave, The second formula is working perfectly. I'm going to test it out on a full data set. Thank you!

And just so I can understand better, can you please tell me what the ROW($A$2:$A$20)-MIN(ROW($A$2:$A$20))+1) parts of the formula do? I was confused on which spreadsheet/data set those were supposed to refer to; and it seems like I got the same result no matter what I changed them to.

Thanks again guys.