If/and in an Excel index function

I am trying to automatically list the contents of the C column in Excel, only if the value in two other columns are "requirement", and "attract". Without the 'and' statement it seems to work, but when I add the AND statement, it just lists all the comments. Probably it influences the row count, but I cannot figure it out. The comments are in Sheet3, the result should be somewhere else.

So in the table above it should only retrieve 'comment 3' and 'comment 4'

Here is the function I am using:

=INDEX(Sheet3!$C$2:$C$127;SMALL(IF(AND(Sheet3!$D$2:$D$127="Requirement";Sheet3!$E$2:$E$127="Attract");ROW(Sheet3!$D$2:$D$127)-ROW(Sheet3!$D$2)+1);ROWS($D$2:$D2)))

Hope you can help!

Solution 1:

Array formula do not play nice with AND or OR as they will take an array and return a single value and we need an array returned.

So we use * and + respectively.

(Sheet3!$D$2:$D$127="Requirement")*(Sheet3!$E$2:$E$127="Attract")

if you have office 365 FILTER would work better:

=FILTER(Sheet3!$C$2:$C$127;(Sheet3!$D$2:$D$127="Requirement")*(Sheet3!$E$2:$E$127="Attract"))

Without FILTER:

=INDEX(Sheet3!$C$2:$C$127;SMALL(IF((Sheet3!$D$2:$D$127="Requirement")*(Sheet3!$E$2:$E$127="Attract");ROW(Sheet3!$D$2:$D$127)-ROW(Sheet3!$D$2)+1);ROWS($D$2:$D2)))