Formula to compare two lookup columns ?

[AuroreLambert]

Hello everyone,

Looking for some help on a problem I'm trying to resolve in Airtable. 

Context and goal of the database

I'm building a database of our members. The first table lists our members and their level of membership and the second table lists the training courses we offer.

Back on the first table, I then inserted a lookup column to say which training course the member has taken. So far so good, everything works.

Here is how it looks like so far (sorry, all in french) :

Our end goal is to send to each member, at the end of the year (when it's time to renew their membership), a list of all our trainings, and for each one, show them which ones they took part of or not. If they took part in the training, it would say "Yes" and if not, it would say "No". We plan on using the page designer extension to build that report.

To do that, I added a lookup column that lists all activities offered so I can insert them in the "page designer" extension. Here is how it looks at this point (the last column shows all the trainings offered):

My question

Is there a formula that will compare the lookup columns "Formations et réseautage" et "Liste de formations" (each column containing many items) and if an item is in both columns, then it says 'Yes' and if it's only in the second lookup columns, then it says 'No' ?

I have tried an If Formula with FIND and AND but I'm just not knowledgeable enough at this point to know what I'm doing wrong... Maybe it's also just not doable at all...

Thanks for your help !

[Elyes80]

Hi @AuroreLambert,

It should be doable, try : 

IF(FIND("Formations et réseautage", {Liste de formations}),"Yes", "No")

[Sho · Accepted solution]

Hi @AuroreLambert ,

I have tried your ideas.

"list" output is not available at the table and must be fine-tuned by line-height.

Formula for the "list" field:

REGEX_REPLACE(
  REGEX_REPLACE(
    TRIM(REGEX_REPLACE(REGEX_REPLACE(menu,"^\\b|, ","@"),"@","\n@")),
    "@(" & SUBSTITUTE({done},", ","|") & ")",
    "Yes"
  ),
  "@.*(\n|$)",
  "\n"
)

 It's getting complicated

[AuroreLambert]

Hi @Sho,

Thanks for your help ; I've tried your solution and the formula you provided works, altough not to the extent I need. There is probably another bit of formula to add that will achieve what I want, i just think that it wil be too complicated down the line, when we add activities in each table.

Considering the complexity of the formula (I have no idea what "Regex_Replace" means and not really that much time to dedicate to learning it), and the fact that it will require a lot of fine tuning to make it work in the page designer extension (and I'm not sure the fine tuning will actually fit for every record of my database), I've decided to arrange my database differently to get to a different solution. Not perfect either, but it's the best we can do considering the time and efforts we want to put into that project.

Thanks for your help though, I appreciate it! I'm gonna mark your reply as the solution to close the thread !