Column to check existence of cell value in another table

[David_Andreae]

I have one table with item names in the first column, and I want to add a column that displays TRUE if the item name is present in the first column of another table of item names.

In Excel I would do this with a VLOOKUP or MATCH wrapped in an IFERROR.

[Elias_Gomez_Sai]

What are you trying to achieve? I mean, could you tell your base structure or maybe real world example?

[Kamille_Parks]

I believe this is more or less what you want to do:

Airtable

Airtable: Organize anything you can imagine

Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.

You will have 3 tables: Table A, Table B, Table C

Table C has one record in it.

Link every record in Table A to that single record in Table C. Link every record in Table B to the single record in Table C as well.

In Table A, create a Lookup field that returns every Table B record linked to the single Table C record. Create a Formula field with the formula IF(SEARCH(Name,{All Records in Table B}), "True","False")

Important: You must make sure that all new records in both Table A and Table B get linked to the unifying record in Table C.

[David_Andreae]

Basically I want to check whether a list of item names in one table A is a subset of item names in another table B, but I don’t want the items in table A to just be lookup values from table B because I want to be able to delete and paste a new updated list into table B at any time, and find out whether it ‘breaks’ anything in table A. In other words, I want table A to indicate whether each item is still in table B or not.
Furthermore, the item names need to be the primary key in both tables.

So I just want to a formula saying “If A.itemname is included in B.itemnames, then TRUE otherwise FALSE”

Sounds rather strange, I know.

[David_Andreae]

thanks Kamille, it sounds a rather indirect way of doing it, but I will give it a go!

[David_Andreae]

actually, I think my requirement of “pasting in a new list in table B at any time” will prevent that solution, because I don’t want to be re-linking everything each time.

[Kamille_Parks]

If you create a view in Table B that is grouped by the field which links to Table C, and do your pasting in that view, all records will be linked upon paste and the formulas should still work.

[Kamille_Parks]

In Airtable formula notation, {This} means: return the value from this table’s field named “This”. The field name {All Records in Table B} is arbitrary, the field could have been named {nothing} and the process described above still would have worked.

To clarify what is going on in this example:

• [Table C] has a record in it which is linked to every record in [Table A] and every record in [Table B] through Link To Another Record two fields
• [Table A] has a Lookup field called {All Records in Table B}. The lookup is based on [Table A]'s connection to [Table C], and is “pointing to” the Link to Another Record field in [Table C] which links it to [Table B].
• [Table A] has a formula field which searches for {Name} within {All Records in Table B}

[sarah_chamberli]

Thanks - I actually JUST deleted my question because I did make some progress. I was confused by some formatting in the original solution but upon the 17th read-through I figured out my error. Now i’ve got a field called foodID and a field next to it (allBabyFoods) that’s a lookup of all the related foodIds from the join table (“Table c”). When I configure my formula, it saves it, but the result is ERROR!

Formula is: IF(SEARCH(foodID,allBabyFoods),“TRUE”,“FALSE”)

The thing is, it doesn’t alert me that the formula is wrong – just each calcuated value is ERROR!

And values are correct. For foodID 57283, i can see the that value in the allBabyFoods column: 57276, 57277, 57279, 57281, 57283, 57284, 57285, 57287, 57291, 57294

[aisportscoach]

This is really complicated. I'm having the same problem. My Table A (with the IF formula) is huge and keeps on being updated. I don't want to manually add the above mentioned lookup file to every record manually. 

Can't I just reference the other table within the "IF(SEARCH(...))" formula?