CODEWITHSUNDEEP
excelfilterexcel-formula
SputnicK
SputnicK

Reputation: 163

How to search and return all matches in a column given multiple search criteria?

enter image description here

In this example, I am searching for the color "red" and returning all instances of the item associated with that color. This was accomplished with the following formula in the Results column: =IFERROR(INDEX($B$2:$B$8, SMALL(IF($D$2=$A$2:$A$8, ROW($A$2:$A$8)-ROW($A$2)+1), ROW(1:1))),"").

I would like to find a way to accomplish this search, but instead return the items corresponding to multiple colors at once (e.g. "blue" and "green"). The problem I am encountering is that changing the search term $D$2 to a range of values seems to break the formula.

I am looking for a modification of this formula or a new formula that can return all matches based on multiple (nth #) search criteria.

Upvotes: 0

Views: 1586

Answers (1)

P.b
P.b

Reputation: 11458

=IFERROR(INDEX($B$2:$B$8, SMALL(IF(ISNUMBER(MATCH($A$2:$A$8,$D$2:$D$3,0)), ROW($A$2:$A$8)-ROW($A$2)+1), ROWS($1:1))),"") By adding ISNUMBER & MATCH you're able to match a list. In this case values from $D$2:$D$3. You can change this range for your demand. I also changed the k-argument for SMALL to ROWS($1:1) to start counting from where you paste your formula and count up when dragged down. The formula should be entered with ctrl+shift+enter as it's an array formula.

Upvotes: 2

Related Questions