My first table records characters from the book series A Song of Ice and Fire. My second table records characters who have died in A Song of Ice and Fire. In the fourth column in my first table, I want to test each row to see if there is a matching row on the second table; if so, that character can be said to have died. Here is invented sample data, so as not to spoil the series:
Characters table
+---------+---------+-----------+---------+
| Title | Name | Surname | HasDied |
+---------+---------+-----------+---------+
| Ser | Jon | Skeet | |
| Lord | Jeff | Atwood | |
| | Leo | King | |
| Maester | Joel | Spolsky | |
| Lady | Experts | Exchange | |
+---------+---------+-----------+---------+
Death table
+---------+---------+-----------+
| Title | Name | Surname |
+---------+---------+-----------+
| | Leo | King |
| Ser | John | Doe |
| Lady | Experts | Exchange |
+---------+---------+-----------+
In the HasDied column of the characters table, I want to check each value of that row - the Title, Name, Surname, and any other arbitrary fields - and if each of the values in the row being evaluated are found in a single row of the death table, then it would output true or false (or some other useful value.) So the characters table should look like this:
Characters table
+---------+---------+-----------+---------+
| Title | Name | Surname | HasDied |
+---------+---------+-----------+---------+
| Ser | Jon | Skeet | FALSE |
| Lord | Jeff | Atwood | FALSE |
| | Leo | King | TRUE |
| Maester | Joel | Spolsky | FALSE |
| Lady | Experts | Exchange | TRUE |
+---------+---------+-----------+---------+
I know that I can use MATCH to find any one value in the death table, but I can't just use three separate MATCH formulas for each column, it's important that the matching values are on the same row. How can I write a formula that would make this search? I'm using LibreOffice Calc, but Excel-based solutions are acceptable if you think they will work in Calc as well.
A suggestion for answering the original question in one go would be the array formula
=NOT(ISERROR(MATCH(CONCATENATE(A2,B2,C2),CONCATENATE(A$11:A$14,B$11:B$14,C$11:C$14),0)))
This concatenates the three parts of each name in the characters table and looks it up in an array formed by concatenating the three parts of each name in the death table.