Copy rows that contain "A" or "B" or "C" in a column into a new sheet

[ygar]

I want to copy rows to another sheet when the text in column B of the main sheet is either Blue, Red or White.
I tried the following for white, but I can't make it work with multiple "OR" matching values.
IF(Working!$B2="White",ROW(),"").
My other formula =IFERROR(INDEX(Working!A:A,SMALL($M:$M,ROW($A1)),1),"")

It could be either a modification to the same formula or a different formula, I basically one to copy the rows that contain Red or Blue or White in column B into a separate spreadsheet.

 

[ygar]

I want to copy rows to another sheet when the text in column B of the main sheet is either Blue, Red or White.
I tried the following for white, but I can't make it work with multiple "OR" matching values.
IF(Working!$B2="White",ROW(),"").
My other formula =IFERROR(INDEX(Working!A:A,SMALL($M:$M,ROW($A1)),1),"")

It could be either a modification to the same formula or a different formula, I basically one to copy the rows that contain Red or Blue or White in column B into a separate spreadsheet.

Sorry, I want to clarify that the rows to copy should contain either *Blue* or *Red* or *White* (partial match)

 

[alansidman]

Consider using Power Query to identify rows that meet requirement and delete all others. If you provide a sample upload with XL2BB then I will provide the Mcode for this to happen.

 

[pmich]

@ygar
If you want to do it manually, then try this.
Say, B$2:B$100 is the search Range where you have text containing either 'Blue' or 'Red' or 'White'.
In C2 type the following formula and press Ctrl + Shift + Enter.
Copy C2 and paste from C3 to C100.
In C1 type Blue and press enter.
This will display the list of partial matches of 'Blue' beginning from C2 .
=IF($C$1="","",IFERROR(INDEX(B$2:B$100,SMALL(IF(ISNUMBER(SEARCH($C$1,B$2:B$100)),ROW(B$2:B$100)-ROW(B$2)+1),ROWS(B$2:B2))),""))
Now copy the list of 'Blue' and using paste values, paste it in the destination sheet.
Again type White in C1 and get results for 'White'.