Compare 5 Tables to Find a Matching Field

[Waterguy]

I have data for 4 competitors and myself , formatted exactly (5 tables).
Each set of data contains a column of keywords. Some competitors have some KWs that match what I rank for. What would be a good way to set up a 6th table or add columns to my table, to indicate a competitor has a matching keyword. Normally I would do a vlookup in Excel, but I think this would be a lot more powerful in Airtable.

Example of Report Needed (I can hide columns if needed):

My Keywords Competitor1 Competitor2
Apples Match No Match
Oranges Match Match
Grapes No Match No Match

I tried linking to each competitors record and copying my KWs in column, and it will add the matched KWs to the competitor tables, but it also adds all the non-matches at the bottom.

Example

My Keywords Link to Competitor1 Link to Competitor2
Apples Apples Apples
Oranges Oranges Oranges
Grapes Grapes Grapes

Any help would be greatly appreciated.

[Kamille_Parks]

Question: why are you using several Tables instead of a single Table with multiple filtered Views?

If you’re adamant about keeping them as separate tables, every Keyword column you have should be converted to a Link to Record field pointing to a table of Keywords. That will show you each unique keyword as its own record and you’ll see which competitors are linked to each keyword.

[Waterguy]

I could create a single table, but then I would have duplicate Keyword values in the same column.
So I could then filter on my and a competitor’s set. But how would I filter on the duplicate keywords?
I’m talking 1000s of KW per company with a hundred matches.

Company | Keyword
Me | Apple
Me | Orange
Me | Grape
Competitor 1 | Grape
Competitor 1 | Mango
Competitor 1 | Pear

[Kamille_Parks]

Again, the Keyword column would link to the Keywords table. In the Keywords table you could have a count field that shows how many companies are linked to it, and then set a filter for when Count > 1