Index Array with one main criteria and two other sub critirea

[juliold]

Hi

I'm working in a course proyect. I have a raw report that i need to sub divde in sheets per course, and in every course sheet i need only the courses "passed" and "in progress". At this time i made a formula to bring the selected course to the sheet, but i need to place a filter in te formula to brin only the passed or in progress.

this is the formula that i'm using to bring the courses per sheet

{=IFERROR(INDEX(Report!B$2:B$3156,SMALL(IF(Report!$B$2:$B$1156=$A$2,ROW(Report!B$2:B$1156)-ROW(Report!B$2)+1),ROWS(Report!B$2:Report!B2))),"")}


i try to use (in test sheet A13) {=IFERROR(INDEX(Report!B$2:B$1156,SMALL(IF(IF(OR((Report!$B$2:$B$1156=$B$2)*(Report!$B$2:$B$1156=$D$2)),(Report!$B$2:$B$1156=$A$2),0),ROW(Report!B$2:B$1156)-ROW(Report!B$2)+1),ROWS(Report!B$2:Report!B10))),"")}

but does not work.
test.csv
So a little bit of help will be great

[daddylonglegs]

I'm a bit confused by your formula because your "OR" is looking to match Report!$B$2:$B$1156 against both B2 and D2, which is OK, but then your next condition is that the same range is equal to A2, that won't be true if the "OR" is true so I wonder if you mean to use different ranges?

In general, though, you can't use OR here because it will return a single result not an array of results as required - you can use + to "simulate" OR, though, e.g. use this as one condition

IF((Report!$B$2:$B$1156=$B$2)+(Report!$B$2:$B$1156=$D$2).....

[juliold]

thanks i will try.

[juliold]

Reporte DC Edu Cont test.xlsxDoes not work. The principal criteria i have it in cell A2, but i need second criteria taht can be two possible matches. The logical process will be: =$A$2, but also equal to $C$2 or $E$2. In other words if Courses ($A$2) are true, then Passed ($C$2) or In Progress ($E$2) needs to be true to.

Any sugestion to build this 3 criteria formula?

for know the single criteria works
{=IFERROR(INDEX(Report!C$2:C$1156,SMALL(IF(Report!$B$2:$B$1156=$A$2,ROW(Report!C$2:C$1156)-ROW(Report!C$2)+1),ROWS(Report!C$2:Report!C15))),"")}

Please find attached the file