I’m working with an Airtable base that contains two separate tables and need help with a lookup formula.
My setup:
Table A (called “Names Database”):
Column 1: “Men” (contains about 30k male names)
Column 2: “Women” (contains about 30k female names)
Table B (called “Contacts”):
Column 1: “Person”
Column 2: “Greeting”
What I need:
For each name in the “Person” column, I want the “Greeting” column to automatically show:
“herr” if the name exists in the “Men” column
“frau” if the name exists in the “Women” column
“unknown” if the name isn’t found in either column
My challenge:
I successfully implemented this in Google Sheets using a nested IF and MATCH formula, but I can’t figure out how to replicate this functionality in Airtable. The formula I used in Sheets was:
Been dealing with similar data matching problems for years. The others covered the main approaches, but there’s another angle worth considering.
Your current structure with separate male/female columns is working against you in Airtable. Stop fighting the platform - work with it instead.
Create a single lookup table with Name and Gender columns like Ryan suggested. Here’s the key - you don’t need to manually restructure 60k records. Export your current Names Database to CSV, do a quick pivot in Excel or Sheets to get the right format, then reimport.
Once you have that structure, linking records in Airtable becomes trivial. The lookup field automatically matches names, and your formula becomes dead simple.
I’ve done this exact transformation with employee databases before. Takes about 30 minutes of prep work but saves hours of scripting or workarounds later.
The automation approach Tom mentioned works too, but you’ll hit rate limits with 60k records and debugging script issues gets annoying fast.
i feel you, Airtable isn’t the best for this kind of stuff. the FIND function might help but i doubt it’ll work like you need it to across tables. maybe try doing the Sheets thing, get your greetings set there then just import em back into Airtable. way simpler.
Airtable doesn’t have a MATCH function like Google Sheets for cross-table lookups. Here’s a better workaround though: restructure your Names Database table with one ‘Name’ column and one ‘Gender’ column (Male/Female values). Then use Airtable’s lookup fields to link records between tables and add a formula field for the greeting: IF({Gender (from Names Database)}='Male','herr',IF({Gender (from Names Database)}='Female','frau','unknown')). You’ll need to restructure your data, but you’ll get proper relational database functionality and way more flexibility. Takes time upfront with 60k names, but it’s much easier to maintain later.
Try Airtable’s automation feature. Set up a script that runs when you add new records to your Contacts table. The script checks both columns in your Names Database and updates the Greeting field. I’ve done this with about 15k records - worked pretty well, though the initial processing took a while. The JavaScript just checks if the name exists in either column and sets the greeting value. Not elegant, but it works. You could also use Zapier to handle the lookup externally, but that’s more complex and costs extra.
I’ve worked with big datasets in Airtable before - here’s a hybrid approach that might work better. Keep your current Names Database but add a third column called “All Names” that combines both male and female names with a formula. Then use Airtable’s FIND function in your Contacts table to search that combined column. Try this formula: IF(FIND({Person}, {All Names from Names Database}), IF(FIND({Person}, {Men from Names Database}), "herr", "frau"), "unknown"). You won’t have to restructure your existing data but you’ll still get the lookup functionality you’re after. Just make sure your names are properly delimited in the combined column so you don’t get partial matches.