Look for name in different table and show something based on if it is found in Airtable

Question

I have a base in airtable with two tables

1. Table 1 is called Data
   it has two columns, one is called Male and one Female Both columns consist of around 30.000 records.

2. Table two is called 'Work'
   It has two columns. One is called Name
   The second is called 'Title'
   If the Name is in the list under Male in the table Data I want it to show 'herr'
   If the Name is in the list under Female in the table Data I want it to show 'frau'
   If it is not found in both I want it to show - no data.

In Google Sheets this was easy to do, in Airtable I cant figure it out. Am I using the wrong tool? Would it be possible?

Google Sheets formula:

=IF(ISNUMBER(MATCH(A2, data!$B$1:$B$30000, 0)), "Frau", IF(ISNUMBER(MATCH(A2, data!$A$1:$A$30000, 0)), "Herr", "no data")))

Edit: this is maybe already to much of a solution. Basically I have a column with first names. I want to automate the process of deciding whether they should be addressed with Dear Sir [name] or Dear Madam [name]. (In German, makes no sense in English ;)). I got a database full of names and their genders from the internet and were able to fix the problem in Google Sheets. However I work in Airtable mostly so would love to do it there as well.

Answer 1

This is not possible using a simple airtable formula. Alternatively you could:

1. Use an airtable automation to search the names table and update the work table based and the search result.
2. You could build the above automation in make.com in conjuction with airtable webhooks.

Another thing I would suggest is to restruction your data table to be called "Names" with 2 columns:

• Name
• Gender (a single select with 2 options, male and female) Restructuring the names table will make it easier to search the data.