Find matching rows between two different data sets in Excel

Question

I have two data sets as shown below. In the first dataset I want to know if the row exists in the 2nd dataset and return either "Found" or "Missing"

Is there any way I can achieve this in Excel?

Answer 1

One way to do this is with a helper column so that you can compare all four cells against a single cell and use straightforward functions. Say your first dataset is in A1:D3 and the second is in F1:I3. In J1, enter:

=F1&G1&H1&I1

Copy this down the column. This will concatenate the four cells into one value. Enter in E1:

=IF(ISNA(MATCH(A1&B1&C1&D1,J$1:J$3,0)),"Missing","Found")

Adjust the J$1:J$3 range to include all of your actual rows and then copy this down the column.

For each row in dataset1, it concatenates the four cells into a single value and looks for a match in the helper column. If there is no match, the ISNA test will be true and it will return "Missing". Otherwise, it means it found a match and it returns "Found".

This screenshot is from LibreOffice Calc, so the layout will look a little different from Excel.