= Excel Filter = The '''`FILTER`''' command subsets rows of data. <> ---- == Usage == === Filters === Given a worksheet like: ||'''A''' ||'''B'''|| ||Alice ||1 || ||Bob ||2 || ||Charlie ||3 || ||Danielle||4 || To subset the rows, try: {{{ =FILTER($A$1:$B$10, $B$1:$B$10>2) }}} The returned value will be an array and [[Excel/Arrays#Spilling|spill]], which is very likely intended here. ---- === Lookups === The function can be used to lookup values from a separate table. Given a worksheet like: ||'''A''' ||'''B'''||'''C'''||'''D''' ||'''E'''|| ||Alice ||1 || ||Alice || || ||Bob ||2 || ||Danielle|| || ||Charlie ||3 || ||Ethan || || ||Danielle||4 || || || || Column `E` can be populated like: {{{ =FILTER($B$1:$B$10, $A$1:$A$10=D1, "NO MATCH ERROR MESSAGE") }}} This works even if the key in column `D` is non-unique. However, if the key in column `A` is non-unique, then the returned value will be an array and spill. See [[Excel/Arrays#Spilling|here]] for help on fixing that. ==== Multiple AND Conditions ==== The second argument to the function is functionally a boolean array. To apply multiple conditions for a match, enclose the conditions in parentheses and multiply them. {{{ =FILTER($B$1:$B$10, ($A$1:$A$10=D1) * ($B$1:$B$10>2), "NO MATCH ERROR MESSAGE") }}} Similarly, to match based on ''any'' of multiple conditions, use addition. {{{ =FILTER($B$1:$B$10, ($A$1:$A$10=D1) + ($B$1:$B$10>2), "NO MATCH ERROR MESSAGE") }}} Note that, if the key in column `A` is non-unique, the returned value is ordered according to their original order, ''not'' according to which condition passed. ---- CategoryRicottone