How to check if two cells of a list are also in another list/range
I have two sources of data, both of which I formatted in the same way. That way, I'm left with two lists of two columns each, ie I have four columns total, say, List1DataType1 (Excel Column A),List1DataType2 (Excel Column B), List2DataType1 (Excel Column C),List2DataType2 (Excel Column D).
Since there are plenty of duplicates, I now want to analyze which rows are in both lists, which rows are in List1 only and which rows are in List2 only. To do so, I thought I could create a new column for each list, with the following formula:
=IF(AND(COUNTIF($A$2:$A$800;C2)>0;COUNTIF($B$2:$B$800;D2)>0);TRUE;FALSE)
Using this formula next to each row of the second list, I thought I would get a result that puts true or false respectively if the double-cell is included in the first list or not. However, I forgot one thing: Each condition of the AND goes through the whole list, which means that it is evaluated as true as long as the first list contains the data on different rows.
Here's an example:
| List1Type1 | List1Type2 | List2Type2 | List2Type2 | My result | Desired result |
|---|---|---|---|---|---|
| A | 1 | A | 2 | T | T |
| A | 2 | B | 1 | T | F |
| B | 2 | B | 3 | F | F |
There's probably some more edge cases I didn't consider in this example, but basically I only want to have a TRUE result if the items really do exist in the other list. How can I do that?
Thanks!
Solution 1:
You can use XLOOKUP to search list 1 for a concatenated expression of your row in list 2. If it returns an error, it is not found. If it doesn't return an error, it is found.
=NOT(ISERROR(XLOOKUP($D5&$E5,$A$5:$A$7&$B$5:$B$7,$A$5:$A$7)))
