How do you create a custom filter formula based on another column's values?

Question

I've got Table A like so:

Names     Age 
-----------------
John      3
Jane      4
Mary      5 
John      6
Chris     7
Mary      8
Michael   9

and Table B like so

Cool Names
--------
Mary
Michael

I want to create a filter on Table A such that it only shows me names that appear in Table B. Which means I should have a resulting view that looks like:

Names     Age 
-----------------
Mary      5 
Mary      8
Michael   9

The formula needs to go

into that box. Here's a sample sheet. Thanks in advance.

I'll accept anything that creates my filter. I've tried putting in =FILTER(A2:A, COUNTIF('Cool Names'!A:A, A2:A)>0) into the field, but it doesn't work. That forumla works if I put it into a normal cell, so it feels like that field is looking for something more specific. Not sure what it is. — vinceh
– vinceh, Commented Nov 2, 2017 at 22:19

pnuts · Accepted Answer · 2017-11-02 22:41:07Z

2

Assuming Names are in ColumnA and Table B is in ColumnG then a custom formula of:

=countif(G:G,A2)

should work.

My preference however would be for the FILTER function:

=FILTER(A2:A,COUNTIF(TableB,A2:A))

where TableB is a named range (in the same document, but not necessarily the same sheet).

(And I'd not bother to cater for headers, =FILTER(A:A,COUNTIF(TableB,A:A)) should be adequate.)

edited Nov 2, 2017 at 22:41

answered Nov 2, 2017 at 22:28

pnuts

59.8k11 gold badges93 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vinceh Over a year ago

This works, but what if Table B is in another sheet?

Collectives™ on Stack Overflow

How do you create a custom filter formula based on another column's values?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related