Skip to main content

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.

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

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






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.

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


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.

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






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.


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

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


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.

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.


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.

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:







  • has a record in it which is linked to every record in
    and every record in
    through Link To Another Record two fields



    • has a Lookup field called {All Records in Table B}. The lookup is based on
    's connection to
    , 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}



    • 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

    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?

     

    Reply


    Terms & Conditions