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.

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

4 Answers 4


You could try something like this:


then copy across and down to suit.


  • This works as a partial result as will return empty strings that should be removed to get the desired final result. Commented Nov 11, 2016 at 17:54




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

Just add the proper headers above the formula

  • I don't believe the ARRAYFORMULA() is necessary around the FILTER()
    – MattKing
    Commented Mar 14, 2022 at 17:16

One cell solution:

Combining the FILTER Function with the COUNTIF function provides you with the list you want:


In this example I used the Key as the matching field comparing column A with matches in D but it works with the other columns as well.


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:


  • 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. Commented Nov 11, 2016 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.