IF statement testing against GIANT list


#1

Hello All,

Very new to the Airtable community- thanks in advance for help. I am trying to build out a table that will check a field against a HUGE list of possible word values and I need a return of TRUE if one of them matches the field. For example if I enter “Acme Corp” into a cell B1, I need B2 to 1) check against a list of 1,000+ names and return a result of “true” if Acme Corp is found in that list, “false” if not. Without creating the most heinous of all nested IF statements since the beginning of time, is this possible??

Thank you!


#2

It’s most definitely possible — in fact, that’s the method underlying my Data De-duplication demo base. (Unfortunately, the document I wrote describing the process was based on an earlier, and undeniably inferior, implementation than the one linked in the opening sentence, above. Still, it describes the general method reasonably accurately, if not the specifics.)

It’s nearly 4 a.m. here, and I’m still a half-hour bus ride from home, so I’ll be brief; if you have any questions, I’ll deal with them in more detail when I’m no longer trying to read through my eyelids. If this was my own personal albatross, I’d create a 1,000-row table where each record was a name from the monster match list. I’d link each record to a single record in a second table——

… … The easiest way to do so: Name the sole record in yr [BigHonkingMatchListRollUp] table ‘.’ — that is to say, period. In your 1,000-row table, define a single-line text field named {Link}. In Row 1 of the {Link} column, type ‘.’. Select that cell and press Ctrl-C. Select the cell immediately below — the {Link} cell in Row 2 — and then scroll to the bottom-most row of the 1,000-row table. While holding down the Shift key, select the {Link} cell in the last row of the table. (A message reading ’1,000 cells [or thereabouts] selected should appear in the lower left of your screen.) Press Ctrl-V; a period is pasted into the {Link} field of all 1,000-ish rows. Now scroll back to the top of the table. Right-click on the {Link} field and select ‘Customize field type.’ Change the field type from single-line text to linked record; when prompted, specify [BigHonkingMatchListRollUp] as the table to which to link. Click ‘Save.’ Airtable grumbles for a few minutes, and the column of periods subtly changes color. They are now active links to the record named ‘.’ in [BigHonkingMatchListRollUp].

—— and use a roll-up field to create one really long field in [BigHonkingMatchListRollUp] containing all the names.——

Define the field {MatchList} In [BigHonkingMatchListRollUp]. Configure it as a rollup field that uses the {LinkBackToTheMainTable} field to roll up the {Name} field using an aggregation function of ARRAYJOIN(values,'|'). Now you have a field that contains one very long string within which are 1,000 or so names, separated by the vertical bar character, ‘|’.

Back in [MainTable], you should have a field called {Name}, which is where you’ll enter such things as 'Acme Corp.'. Following it, you should define a field called {Match}. {Match} is a rollup field as well, using {Link} to roll up {MatchList} in, you guessed it, [BigHonkingMatchListRollUp]. {Match} should have the following as its aggregation function:

IF(
    FIND(
        {Name},
        values&''
        ),
    '✅',
    BLANK()
    )

Now, whenever you enter a name into, um, {Name}, if that name can be found in your big, honking, match list, a ‘:white_check_mark:’ — the White Heavy Check Mark emoji — will appear beside it. If the name does not appear in the list, the field will remain blank.


Hmmm… I guess it’s a good thing I avoided going into detail, eh?

Anyway, that stream-of-consciousness base design will do what you want — assuming you can make any sense of it.


#3

Wow- thank you so much @W_Vann_Hall! This is fantastic. I really appreciate you taking time out at 4am to help me out! THANKS!!