# Compare two lists and display missing items in a Google Spreadsheet

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)".

• 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.

• Why doesn't vlookup do the job for you? – rahi Nov 4 '16 at 0:48
• @rahi Thank you for looking at this. vlookup might work, can you please give me some direction? – Mr. B Nov 4 '16 at 2:24

You could try something like this:

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

then copy across and down to suit.

COUNTIF

• This works as a partial result as will return empty strings that should be removed to get the desired final result. – Rubén Nov 11 '16 at 17:54

# Formula

``````=ArrayFormula(FILTER(A4:C7,ISERROR(match(A4:A7,D4:D5,0))))
``````

# Explanation

MATCH returns an error if the invited doesn't appear on the attended list. ISERROR converts errors to TRUE and values to FALSE This result is used as the filtering criteria. The result is, the list of absents:

``````|   001 |Tim       |Smith
|   003 |Amy       |Wilson
``````

VLOOKUP is another way you can get the answer you want, but I'm not sure if it presents it the way you want.

From the Google Sheets help article, VLOOKUP,

Searches down the first column of a range for a key and returns the value of a specified cell in the row found.

So if you add a `Status` column on your list of attendees you can run the `VLOOKUP` on the invited list, asking it to return the `Attended` status.

Your data could look something like this:

In cell D2, your `VLOOKUP` formula would look like this:

`=VLOOKUP(A2, E:H, 4, FALSE)`

• `A2` is the key
• `E:H` is the range (the list of attendees)
• `4` refers to the column number in the range of the value you want to return
• `FALSE` indicates that you're looking for an exact match on the key

When you fill the formula down from cell D2 to D5, you end up with data looking like this:

Where there was a match, the `Status` value gets pulled over. Where there's no match, you see an `#N/A`.

• This works as a partial result as will return errors that could be used to later filter the invited list in order to get the desired final result. – Rubén Nov 11 '16 at 17:56