Pull whole rows of data to another sheet that match any of multiple criteria

I have been using a method I found in this post on one of my spreadsheets for a while and it has worked great, however I now have a need to do exactly this same thing - except to pull whole rows based on one of 2 criteria. For instance, sticking with the original example sheet that was used in the other post (that I've attached here:281380-pull-rows-from-one-sheet-to-another-sheet-based-on-value-in-column-test-data-v3.xlsx) If I wanted all "Blue" tasks AND all "yellow" tasks to pull to the same sheet, how would I go about setting multiple criteria for the formula to pull all rows matching either criteria? I have played around with adding the second criteria to the next "helper" cell just below my main criteria and in the cell just to the right of my main criteria cell and trying to use the "OR" function, but I am not getting the desired results. Can anyone help me? Thanks!

Please Login or Register  to view this content.

the cell b1 would contain the word "yellow"

Try


=IFERROR(INDEX('Test Data'!A$10:A$50,SMALL(IF(('Test Data'!$A$10:$A$50=$A$1)+('Test Data'!$A$10:$A$50=$B$1),ROW('Test Data'!$A$10:$A$50)-MIN(ROW('Test Data'!$A$10:$A$50))+1),ROWS($1:1))),"")


B1 = Yellow

Originally Posted by rcm

Please Login or Register  to view this content.

the cell b1 would contain the word "yellow"

Thank you for your reply. Your formula is exactly what I had already tried - and it doesn't give me the correct result. This was my problem with this method as well. Instead of pulling the appropriate results from the list, it just pulls the first result from the list.

Originally Posted by JohnTopley

Try


=IFERROR(INDEX('Test Data'!A$10:A$50,SMALL(IF(('Test Data'!$A$10:$A$50=$A$1)+('Test Data'!$A$10:$A$50=$B$1),ROW('Test Data'!$A$10:$A$50)-MIN(ROW('Test Data'!$A$10:$A$50))+1),ROWS($1:1))),"")


B1 = Yellow

thank you for your reply as well, however with this formula, I likewise only get the first thing in the list, not the appropriate result.

Originally Posted by newdoverman

If you want to enter the criteria in A1 and B1 try this variation:

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

The first formula gave me no result at all, but this one did work and is working just as I had hoped! I knew to enter CTRL+SHIFT+ENTER for the array, but maybe I failed to do that when JohnTopley posted his solution, because I think his formula was the same as this one. Anyway, thank you both very much!