6

I have a list of teams and their corresponding owners in Google Sheets.
(owners own multiple teams)

I have a great formula that will filter the list by owner name:
=FILTER(A2:A6, MATCH(B2:B6,"RINGO",0))

I like using filter in case I had stat columns for the team that I wanted to propagate as well.

My problem is that I actually have TWO sets of data, and they are not on the same column.

  A         B          C          D
GIANTS    RINGO     ROCKETS     GEORGE
DODGERS   GEORGE    BLUE JAYS   PAUL
PACKERS   PAUL      DUCKS       RINGO
KINGS     PAUL      YANKEES     JOHN
BLAZERS   JOHN      LAKERS      JOHN
OILERS    RINGO     BRUINS      GEORGE

I tried using brackets {_,_} to expand my ranges:
=FILTER({A2:A6,C2:C6}, MATCH({B2:B6,D2:D6},"RINGO",0))
But I get the error "FILTER range must be a single row or single column."

I've been searching for a while now and can't seem to come up with a formula that will give me one list for Ringo: GIANTS OILERS DUCKS

Ideas?

Improve this question
1
  • Welcome. Please do share a link to a sample spreadsheet as well as the desired result. It would be great help to anyone trying to answer your question. Also, as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well -. (Why vote?)
    – marikamitsos
    Sep 3, 2019 at 19:30

1 Answer 1

Reset to default
4

You have to "stack" your columns.

You can do that using the following formula: 

=FILTER({A4:A10;C4:C10}, MATCH({B4:B10;D4:D10},"RINGO"))

EDIT (following your comment): 

=SORT(FILTER({A4:A10;C4:C10}, MATCH({B4:B10;D4:D10},"RINGO")))

Improve this answer
5
  • THANK YOU! I was using , instead of ;
    – Farmer
    Sep 3, 2019 at 19:57
  • "I was using , instead of ;". Correct. The way you had your formula you were producing rows.
    – marikamitsos
    Sep 3, 2019 at 20:08
  • One more thing... the formula has to have the sorting feature to get the desired list. =FILTER({A4:A10;C4:C10}, MATCH({B4:B10;D4:D10},"RINGO",0))
    – Farmer
    Sep 3, 2019 at 20:23
  • Try to use =SORT() on your output
    – pjmg
    Sep 3, 2019 at 20:31
  • In your question your results were not sorted. In any case try the edited formula.
    – marikamitsos
    Sep 3, 2019 at 20:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.