Check if value exists in multiple columns across tables and return different results in Airtable

Other Questions Airtable
1

I’m working with an Airtable database that has two separate tables and I need help with a lookup formula.

My setup:

Table A is named “People” and contains:

  • Column called “Boys” with about 30k entries
  • Column called “Girls” with about 30k entries

Table B is named “Tasks” and has:

  • Column called “PersonName”
  • Column called “Greeting”

What I want to achieve:

For each PersonName in table Tasks, I need the Greeting column to automatically show:

  • “sir” if the name appears in the Boys column from People table
  • “madam” if the name appears in the Girls column from People table
  • “unknown” if the name is not found in either column

My Google Sheets solution worked like this:

=IF(ISNUMBER(MATCH(B2, people!$A$1:$A$30000, 0)), "sir", IF(ISNUMBER(MATCH(B2, people!$B$1:$B$30000, 0)), "madam", "unknown"))

This formula checked if PersonName existed in either column and returned the appropriate greeting. I can’t figure out how to replicate this logic in Airtable. Is this even possible or should I stick with Google Sheets for this type of cross-table lookup?

2

I’ve done similar cross-table lookups in Airtable - it works totally different from Google Sheets. You need to restructure your data first. Instead of separate Boys and Girls columns, create one table with a Gender field. Then set up a lookup field from Tasks to People and add a formula: IF(Gender=‘Boy’, ‘sir’, IF(Gender=‘Girl’, ‘madam’, ‘unknown’)). Searching across multiple columns in separate tables is a nightmare in Airtable since it’s built for relational data, not range formulas like Sheets. The restructuring feels like extra work but it’ll make your database way easier to maintain and actually use Airtable how it’s meant to work.