Column to check existence of cell value in another table


#1

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.


#2

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


#3

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

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.


#4

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.


#5

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


#6

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.


#7

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.