Add Condition To Formula To List All Matches

[surkdidat]

I have the following formula, however, I want to add another condition that in Column K, the value has to be "CORRECT" for all matches.

IF($C$37="CR Number",
RIGHT(INDEX('[Request Tracker.xlsm]In Progress'!$A$2:$A707,
SMALL(IF($C$25='[Request Tracker.xlsm]In Progress'!$J$2:$J707,
ROW('[Request Tracker.xlsm]In Progress'!$A$2:$A707)-ROW('[Request Tracker.xlsm]In Progress'!$A$2)+1),
ROW('[Request Tracker.xlsm]In Progress'!1:1))),7),"")

Thanks!

 

[Peter_SSs]

To answer your question, I believe this will do it, but you will have to add back in the relevant workbook name as i have just tested in a single workbook.

=IF($C$37="CR Number",
RIGHT(INDEX('In progress'!$A$2:$A707,
SMALL(IF($C$25='In progress'!$J$2:$J707,IF("CORRECT"='In progress'!$K$2:$K707,
ROW('In progress'!$A$2:$A707)-ROW('In progress'!$A$2)+1)),
ROW('In progress'!1:1))),7),"")

Some extra comments though:

1. Assuming that this formula is not in the sheet '[Request Tracker.xlsm]In Progress'! then using ROW('[Request Tracker.xlsm]In Progress'!1:1) to get the 1st, 2nd, 3rd etc value is risky. If a new row is added at the top of that In Progress worksheet, all your formulas will return incorrect results. A more robust suggestion is included below.
   
   
2. Since you have Excel 2016 you can do this using a non-array-entered and shorter formula using the AGGREGATE function. It would take this form
   Again the other workbook reference would need to be added in.
   
   

   =IF($C$37="CR Number",RIGHT(INDEX('In progress'!A:A,AGGREGATE(15,6,ROW('In progress'!$A$2:$A$707)/
   (('In progress'!$J$2:$J$707=$C$25)*('In progress'!$K$2:$K$707="CORRECT")),ROWS(C$1:C1))),7),"")

   
   In relation to my robustness point above: The C$1:C1 reference near the end of that formula should be the address of the first cell that contains this formula. That is, for me my first formula is in cell C1 and then copied down.