Copy other cells if EITHER column has specific text

Question

In a single sheet wanting to copy D3:N if either B3:B or C3:C = S2. I'm sure there is a simple formula to do this I am just unable to find it!

Answer 1

Try a formula like the following:

=FILTER(D3:N,(B3:B=S$2)+(C3:C=S$2))

Since FILTER conditions are treated as ANDs, using in your two conditions directly would only let rows through if B3:B AND C3:C matched S2. Instead I use a plus sign to add TRUEs and FALSEs since that works as the Boolean OR we need.

Here's the above formula showing matching rows starting in V3:

Answer 2

• you can solve this easily with QUERY formula:

  =QUERY(D3:N; "where B='"&S2&"' or C='"&S2&"'"; 0)

• if S2 cell is a number then it would be:

  =QUERY(D3:N; "where B="&S2&" or C="&S2; 0)