CODEWITHSUNDEEP
arraysexcelif-statementexcel-formulaexcel-2016
Valentyn
Valentyn

Reputation: 1

Excel Array formula IF with multiple criterias

In my spreadsheet using array formula I am creating a unique list of values based on multiple conditions. Formula is below:

{=INDEX(INDIRECT($O$3&"!$L$2:$L$"&$O$16),SMALL(IF((INDIRECT($I$3,FALSE)=$O$7)*(INDIRECT($K$3,FALSE)=$O$9)*(INDIRECT($M$3,FALSE)=$O$11)*(INDIRECT($X$17,FALSE)=$O$15)*(INDIRECT($AF$17,FALSE)>$O$15),ROW(INDIRECT($O$3&"!$L$2:$L$"&$O$16))-2,""),ROW()-20))}

Question belongs to this part if IF function:

(INDIRECT($K$3,FALSE)=$O$9)

Cell $O$9 contains drop down which includes one of cell content variables as well as <>, <>*, <> * Text*,""

When I use direct text match: specific column contains list of fruits and "apple" is one of the values, once $O$9 contains word "apple" formula works and I get a unique list. Should $O$9 contain any of above mentioned combinations (<>, <>*, <> * Text*,"") it gives me an error.

Question: How to change "=$O$9" so that it will be able to use following content of the $O$9 equals to <>, <>*, <> * Text*,"", etc.

Note: I cannot adjust drop down in cell $O$9 but can modify array formula only.

Thanks in advance!

Upvotes: 0

Views: 527

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34380

Well this is by no means a complete answer but just to show my thinking:-

Suppose you have two column ranges which I have called AA and BB and you want to implement some of your tests based on $O$9. It would look something like this:-

=SUM((BB=1)*IF($O$9="<>",AA<>"",IF($O$9="<>*",AA="",IF(ISNUMBER(FIND("*",$O$9)),ISERROR(FIND(MID($O$9,4,LEN($O$9)-1),AA)),$O$9=AA))))

So what I am saying is that your (INDIRECT($K$3,FALSE)=$O$9) would have to become something like the contents of the brackets following SUM above.

Upvotes: 1

Related Questions