INDEX, SMALL Functions with Multiple Criteria

[jworkman7]

The below formula returns #Value. The problem is in the bolded portion of the formula. It works if I remove the AND and do only 1 criteria, but I can't make it work with two criteria

=INDEX(Sheet3!$D$3:$D$1031,(SMALL(IF(AND(Sheet3!$S$3:$S$1031="YES",Sheet3!$Q$3:$Q$1031=E46)),ROW(Sheet3!$D$3:$D$1031)-ROW(Sheet3!$D$3)+1),ROWS(Sheet3!$D$3:$D3))))

I've also tried the below, but it also returns an error:

=INDEX(Sheet3!$D$3:$D$1031,(SMALL(IF((Sheet3!$S$3:$S$1031="YES")*(Sheet3!$Q$3:$Q$1031=E46)),ROW(Sheet3!$D$3:$D$1031)-ROW(Sheet3!$D$3)+1),ROWS(Sheet3!$D$3:$D3))))

Please help. Thanks

 

[FDibbins]

1st are you entering this using CTRL SHIFT ENTER, and not just enter?

2nd, this should work...
=INDEX(Sheet3!$D:$D,(SMALL(IF((Sheet3!$S$3:$S$1031="YES")*(Sheet3!$Q$3:$Q$1031=E46)),ROW(Sheet3!$D$3:$D$1031)),ROWS(Sheet3!$D$3:$D3)))

 

[jworkman7]

1st are you entering this using CTRL SHIFT ENTER, and not just enter?

2nd, this should work...
=INDEX(Sheet3!$D:$D,(SMALL(IF((Sheet3!$S$3:$S$1031="YES")*(Sheet3!$Q$3:$Q$1031=E46)),ROW(Sheet3!$D$3:$D$1031)),ROWS(Sheet3!$D$3:$D3)))

FD, your formula works, thank you! However,it didn't work at first. I discovered that I had errors in column Q,which was causing the#Value. I inserted an IFERROR and it works now.

 

[FDibbins]

Great stuff, happy to help