Imagine that we have the following 2 lists each consisting of 3 columns of data:

And we need to find those items that are in List 1 that are in List 2. However ALL 3 columns of data must match for items to be in both lists. One way would be to construct a comprehensive IF function. However this could get to be a messy formulae.

An alternative would be to construct some helper columns . Helper columns are additional columns that can reduce the need for complex or cumbersome formulae. In this case the Helper columns are the individual list items concatenated into a single string.

So in cell D6 we have:
D6 = A6&” “&B6&” “&C6 and:
I6 = F6&” “&G6&” “&H6
And then copy down:

And then we can put in column L:
=IF(ISNUMBER(MATCH(I6,$D$6:$D$16,0)),”MATCH”,”")

So when there is a match we between the item in Column I and Column D, we get the word “MATCH” appearing – otherwise we get blank space:

To download the .XLSX file for this article, click here