How to filter a lookup list by all the values of a column

1

I would think this is easy but can’t figure it out. Anybody know the formula?

Table 1 is a list of prospects and who referred those prospects.
Table 2 is a list of customers

Column 2 in table 2 shows all the prospects that the customer referred by a simple lookup formula.

What I need to do it is filter that lookup list to only show all the prospects that are also in the customer list and therefore they are no longer prospect, but the have become customers.

Is there an easy way to do this with a filter formula?

Thanks!
Dominic

Finding referred customers
Finding referred customers2971×2485 359 KB

2

Dear @Dominic_Symons1

Welcome to the community!

I recommend to have a look to this recording of “formula fitness”, as it very well explains the logic.

In case questions remain, don’t hesitate to let us know and we will do our best to support.

3

Hi @Dominic_Symons1,

Give a formula like this a try. I think it’s what you’re looking for…

[My Table].[List 1].Filter(CurrentValue.Contains([My Table].[List 2]) AND CurrentValue.IsNotBlank())

If you are looking for those in List 1 and NOT in List 2, then you can add the Not() in the formula…

[My Table].[List 1].Filter(CurrentValue.Contains([My Table].[List 2]).Not() AND CurrentValue.IsNotBlank())

1 Like
4

Thanks for the solutions. I have brushed up on my skill and got it working.

1 Like