Google Sheet Array Formula with an If statement

Question

I'm trying to populate one sheet with data from another based on one column using an array formula.

My Data (simplified sample) looks like this:

I know I can merge the data together with this formula.

=unique({(B6:D);(F6:H)})

But I only to pull the rows that have refund in the 3rd column.

I've done some googling and managed to come up with this:

=unique({(if(D6:D='Refund',B6:D);(if(H6:D='Refund',F6:H)})))

But that's not working and I can't figure out how to do this. I've seen some entries that mention using the filter function but that didn't work for me either.

Answer 1

Try:

=query(unique({(B6:D);(F6:H)}),"where Col3 contains 'Refund' ",0)