Check if value exists in another table and display conditional result in Airtable

[JessicaDream12]

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

My setup:

Table A is named “Records” and contains:

• Column “Men” with about 30k entries
• Column “Women” with about 30k entries

Table B is named “Tasks” and has:

• Column “Person”
• Column “Status”

What I want to accomplish: For each entry in the “Person” column, I need the “Status” column to automatically display:

• “mr” if the person is found in the “Men” column from Records table
• “ms” if the person is found in the “Women” column from Records table
• “unknown” if the person isn’t found in either column

I had this working perfectly in Google Sheets using this formula:

=IF(ISNUMBER(MATCH(A2, records!$B$1:$B$30000, 0)), "Ms", IF(ISNUMBER(MATCH(A2, records!$A$1:$A$30000, 0)), "Mr", "unknown"))

But I can’t figure out how to recreate this logic in Airtable. Is this even possible with Airtable’s formula system? Should I be using a different approach or tool for this type of cross-table lookup?

[Mia92]

totally agree, linked records can be a pain! linking and using SEARCH() can help, but honestly, merging all data into a single table is a lot simpler, especially for gender filtering. way less hassle!