Listing out multiple matches from multiple search criteria...??

[alexx579]

Hi all, I have Report A....it has 4 columns of data, A: Date, B: Rating, C: Type, and D: Finding....with 215 rows of information.
In report B, I have a running list with the same breakdown, but 132 rows of data. In report B, columns A-C have different combinations still contained within Report A, but many repeat themselves. I want to pull in the Findings from Report A (those are all different) even if A-C repeat. I have tried many INDEX/MATCH formulas I saw online, but often it only pulls in the first value. These repeats can occur in different rows in Report B. Can someone help???

 

[jarjarbingie]

Hi all, I have Report A....it has 4 columns of data, A: Date, B: Rating, C: Type, and D: Finding....with 215 rows of information.
In report B, I have a running list with the same breakdown, but 132 rows of data. In report B, columns A-C have different combinations still contained within Report A, but many repeat themselves. I want to pull in the Findings from Report A (those are all different) even if A-C repeat. I have tried many INDEX/MATCH formulas I saw online, but often it only pulls in the first value. These repeats can occur in different rows in Report B. Can someone help???

try this:

=IFERROR(INDEX('Report A'!$D$2:$D$10,SMALL(IF(('Report A'!$A$2:$A$10=$A2)*('Report A'!$B$2:$B$10=$B2)*('Report A'!$C$2:$C$10=$C2),ROW('Report A'!$D$2:$D$10),""),1)-1),"")&IF(ISERROR(INDEX('Report A'!$D$2:$D$10,SMALL(IF(('Report A'!$A$2:$A$10=$A2)*('Report A'!$B$2:$B$10=$B2)*('Report A'!$C$2:$C$10=$C2),ROW('Report A'!$D$2:$D$10),""),2)-1)),"",", "&INDEX('Report A'!$D$2:$D$10,SMALL(IF(('Report A'!$A$2:$A$10=$A2)*('Report A'!$B$2:$B$10=$B2)*('Report A'!$C$2:$C$10=$C2),ROW('Report A'!$D$2:$D$10),""),2)-1))&IF(ISERROR(INDEX('Report A'!$D$2:$D$10,SMALL(IF(('Report A'!$A$2:$A$10=$A2)*('Report A'!$B$2:$B$10=$B2)*('Report A'!$C$2:$C$10=$C2),ROW('Report A'!$D$2:$D$10),""),3)-1)),"",", "&INDEX('Report A'!$D$2:$D$10,SMALL(IF(('Report A'!$A$2:$A$10=$A2)*('Report A'!$B$2:$B$10=$B2)*('Report A'!$C$2:$C$10=$C2),ROW('Report A'!$D$2:$D$10),""),3)-1)) press CTRL+SHIFT+ENTER

it can accommodate up to 3 values..