How to search Airtable records when users provide partial compound last names

Other Questions Airtable
1

I have a web form that collects firstName and lastName from users. I need to search an Airtable database of event attendees using these values. The problem is that many attendees have compound surnames separated by hyphens like smith-johnson. When someone fills out the form, they might only enter the first part of their compound name like smith instead of the full smith-johnson. This causes my Airtable query to fail since it can’t find an exact match. I’m trying to use a filterByFormula with REGEX to match the partial surname against the full compound names in the database. Here’s what I attempted:

`(AND({firstName} = "${firstName}",SEARCH(LEFT("${lastName}",FIND("-",{lastName})-1),{lastName})>0))`

This approach isn’t working as expected. Has anyone solved a similar partial matching issue with compound names in Airtable? I need a way to match partial surnames against the portion before the hyphen in stored compound surnames.

2

I hit the same issue building a customer lookup system. You’re mixing up your field references.

Try this:

AND({firstName} = "${firstName}", OR({lastName} = "${lastName}", FIND("${lastName}", {lastName}) > 0))

This does exact match first, then partial match. Works great for compound names.

You could also use REGEX_MATCH:

AND({firstName} = "${firstName}", OR({lastName} = "${lastName}", REGEX_MATCH({lastName}, "^${lastName}(-.*)?$")))

This matches names starting with your input, plus optional hyphen and extra text.

I’d go with the first one - it’s cleaner and faster. REGEX gets slow with big datasets.

This video covers dynamic filtering really well:

One tip - create a separate field with just the first part of compound names. Makes queries way faster and more reliable.