How to check if value exists in another table and display result based on match in Airtable

Other Questions Airtable
1

I’m working with an Airtable base that contains two tables and need help with conditional lookups.

My setup:

Table A (called “Names”):

  • Column 1: “Men” (contains about 30k entries)
  • Column 2: “Women” (contains about 30k entries)

Table B (called “Tasks”):

  • Column 1: “Person”
  • Column 2: “Greeting”

What I want to achieve:

  • When a name from “Person” column matches an entry in the “Men” column, display “herr”
  • When a name matches an entry in the “Women” column, display “frau”
  • If no match is found in either column, show “no data”

I managed to do this in Google Sheets using this formula:

=IF(ISNUMBER(MATCH(C2, names!$D$1:$D$30000, 0)), "Frau", IF(ISNUMBER(MATCH(C2, names!$C$1:$C$30000, 0)), "Herr", "no data"))

But I can’t figure out how to replicate this logic in Airtable. Is this even possible? I’m trying to automate gender-based salutations for German correspondence where you need “Herr” or “Frau” before the name.

2

Yeah, you can’t do direct cross-table matching like Google Sheets in Airtable - it just doesn’t work that way. The other suggestions won’t cut it either. FIND doesn’t work across tables, and multiple lookups get messy fast with 30k records.

Here’s what actually works: Make a third table called “All Names” with Name and Gender columns (Herr/Frau). Dump all your men/women data in there, then link it to your Tasks table through the Person field. Use a rollup or lookup to grab the gender.

It’s more upfront work, but it’ll handle your 30k records way better than trying to hack together nested formulas across separate tables. Plus it’s much easier to maintain.

3

That approach won’t work since you’re using separate tables. You need lookup fields first to connect your tables. In your Tasks table, add two lookups to the Names table - one for Men, one for Women. Then use a formula with nested IFs: IF({Person} = {Men Lookup}, "herr", IF({Person} = {Women Lookup}, "frau", "no data")). But honestly, this gets messy with 30k entries. Better solution: restructure your Names table into one column with a Gender field, then use a single lookup. Way cleaner and performs much better with large datasets.

4

Been dealing with similar cross table lookups for years and hit this exact wall with Airtable. You’re fighting against how Airtable works.

I’d flip the whole approach. Instead of checking if a name exists in either the Men or Women columns, create a single lookup field in your Tasks table that connects to your Names table. But first, restructure that Names table.

Combine your Men and Women columns into one “Name” column, then add a “Title” column with “herr” or “frau” values. Now you can link Person to Name and pull the Title with a simple lookup.

If restructuring feels like too much work, there’s a workaround. Use Airtable’s automation feature. Set up an automation that triggers when Person gets filled in, then use a script to check both columns and update the Greeting field.

The script approach lets you keep your current structure but gives you the conditional logic you need. Plus it handles the 30k records without the performance hit you’d get from complex formulas.

5

for sure, airtable can be tricky! you could use a formula like IF(FIND({Person}, {Men}), 'herr', IF(FIND({Person}, {Women}), 'frau', 'no data')). that should help simplify your matches!