Rashid asks:I used your array formula with great success to find the search results from multiple criteria. However, my problem is modifying your formula. In the above example you have shown us, you have two criteria. And you distinguish the two criteria by using *.
My question is: what would you do if you don't know the predetermined number of criteria. So let's say the person searching only specifies security and not date. Or only date, and not security. Or maybe both. The problem is you don't know from beforehand.
How would you go about solving this problem?
Your help is greatly appreciated, thanks so much!!
This means that there are two criteria matching on row 3 in the table
Step 2 - Count the number of criteria matching and compare with each value in the array above
The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).
Formula in B14: =INDEX(D3:D6, SUMPRODUCT(--(C10=B3:B6), --(C11=C3:C6), ROW(D3:D6)-MIN(ROW(D3:D6))+1)) Alternative array formula #1 in B15: =INDEX(D3:D6, MATCH(C10&"-"&C11, B3:B6&"-"&C3:C6, 0)) Alternative array formula […]