3

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?

  • 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 at 19:30
1

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")))

  • THANK YOU! I was using , instead of ; – Farmer Sep 3 at 19:57
  • "I was using , instead of ;". Correct. The way you had your formula you were producing rows. – marikamitsos Sep 3 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 at 20:23
  • Try to use =SORT() on your output – pjmg Sep 3 at 20:31
  • In your question your results were not sorted. In any case try the edited formula. – marikamitsos Sep 3 at 20:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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