Google Sheets – Compare Two Lists and Display Missing Items

formulasgoogle sheetsworksheet-function

I'm trying to find a solution to compare two separate lists (invited – attended) and create a 3rd list (absent).

The function must look at the key in "Attended List (D,E,F)" and compare it with the key in "Invited List (A,B,C)" and then display missing values in "Absent List (G,H,I)".

About the data

  • Key is a 'unique value' assigned to each invited person.
  • The data in "Invited List (A,B,C)" is comprised of QUERY data.
  • The data in "Attended List (D,E,F)" it copy/pasted values in the cells.

Example Of Desired Outcome

Invitded List (A,B,C)      Attended List (D,E,F)       Absent List (G,H,I)
 A      B       C            D      E       F            G      H        I
Key | Fname | Lname       | Key | Fname | Lname       | Key  | Fname | Lname
------------------------------------------------------------------- 
001   Tim     Smith         002  Mike     Jones         001    Tim     Smith
002   Mike    Jones         004  Jenny    Johnson       003    Amy     Wilson
003   Amy     Wilson
004   Jenny   Johnson

Any assistance is greatly appreciated.

Best Answer

You could try something like this: 

=if(countif(D:D,A2)=0,A2,"")

then copy across and down to suit.

COUNTIF

Related Topic